Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

434
Visualizações
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 Respostas
Responde à pergunta

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 Relatório

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 Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda