Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

430
Vistas
is there a way I can update multiple non-contiguous google sheet ranges using apps script all at the same time? (using SpreadsheetApp not sheets api)

lets assume we have a table of 4 x 4 starting from A1, now normally if I want to update it's values at once I'll just do

sheet.getRange("A1:D4").setValues(values);

now if the data is not contiguous so I have 3 4 x 4 separate tables with other data between them but I have them named as named ranged i.e (table1, table2, table3), can I do sth like:

sheet.getNamedRanges([table1range, table2range, table3range]).setValues([table1data, table2data, table3data]);

instead of:

sheet.getRange(table1range).setValues(table1data);
sheet.getRange(table2range).setValues(table2data);
sheet.getRange(table3range).setValues(table3data);

so that now all the 3 tables will be updated in the same operation instead of 3 different operations to save execution time? thank you in advance.

about 4 years ago · Juan Pablo Isaza
2 Respuestas
Responde la pregunta

0

According to Tanaike's proposal, I suggest a slight modification

  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getActiveSheet()
  const ranges = [table1range2D, table2range2D, table3range2D];
  const values = [data1values2D, data2values2D, data3values2D];
  const data = ranges.map((e, i) => ({ range: `'${sheet.getName()}'!${e}`, values : values[i] }));
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, ss.getId());

for instance

function updateGoogleSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getActiveSheet()
  const otherSheet = ss.getSheetByName('other')
  const table1range2D = 'A1:B2', table2range2D = 'C4:D5', table3range2D = 'E7:F8'
  const data1values2D = otherSheet.getRange('A1:B2').getValues(), data2values2D = otherSheet.getRange('C4:D5').getValues(), data3values2D = otherSheet.getRange('E7:F8').getValues()
  const ranges = [table1range2D, table2range2D, table3range2D];
  const values = [data1values2D, data2values2D, data3values2D];
  const data = ranges.map((e, i) => ({ range: `'${sheet.getName()}'!${e}`, values : values[i] }));
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, ss.getId());
}

where in sheet named 'other' we can find in A1 =sequence(10,10,0,1)

enter image description here

about 4 years ago · Juan Pablo Isaza Denunciar

0

I believe your goal is as follows.

  • You want to achieve the script like sheet.getNamedRanges([table1range, table2range, table3range]).setValues([table1data, table2data, table3data]); in order to reduce the process cost.
  • Namely, you want to put the values for each range to Google Spreadsheet using Google Apps Script. And, the range is

In this case, I think that Sheets API can be used for achieving your goal as follows. In this case, this thread might be useful. Ref When Sheets API is used for your situation, the process cost can be reduced rather than that of the method for putting the values to each range in the loop using Spreadsheet Service (SpreadsheetApp). Ref

const spreadsheetId = "###"; // Please set the Spreadsheet ID.
const ranges = [table1range, table2range, table3range]; // Please set table1range, table2range, table3range as A1Notation.
const values = [table1data, table2data, table3data]; // Please set table1data, table2data, table3data.
const data = ranges.map((e, i) => ({range: e, values: [[values[i]]]}));
Sheets.Spreadsheets.Values.batchUpdate({data, valueInputOption: "USER_ENTERED"}, spreadsheetId);

But, in this case, I thought that it might be difficult a little to create the request body. So I created a Google Apps Script library as a wrapper for using Sheets API. When this library is used, the sample script is as follows.

1. Install library.

You can see the method for installing the library at here. And, this script uses Sheets API. So please enable Sheets API at Advanced Google services.

2. Sample script:

const ranges = [table1range, table2range, table3range]; // Please set table1range, table2range, table3range as A1Notation.
const values = [table1data, table2data, table3data]; // Please set table1data, table2data, table3data.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
RangeListApp.getSpreadsheet(spreadsheet).getRangeList(ranges).setValues(values);
  • When I saw your script, I thought that this sample script might be in the same direction you expect.
  • When this script is run, each value of values is put to each range of ranges using Sheets API.

References:

  • Benchmark: Reading and Writing Spreadsheet using Google Apps Script
  • Method: spreadsheets.values.batchUpdate
  • RangeListApp
about 4 years ago · Juan Pablo Isaza Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda