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)

Question

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.

You modified the question after it was closed but the answer you seek is found [here](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRangeList(String)) — Cooper, Jan 29 '22 at 23:49
@Cooper that does not answer it because getRangeList() can only be used with setValue() and not setValues() so it is not helping in this case, thank you. — , Jan 30 '22 at 00:01
There is no solution that works with setValues() unless the cells in the range are adjacent and can be arranged in a rectangular array — Cooper, Jan 30 '22 at 00:11
okay thank you, I believe this is a different question though as the 2 questions have different answers and one is by using SpreadsheetApp while the other is by using sheets service — , Jan 30 '22 at 00:25
In your situation, this library is useful? https://github.com/tanaikech/RangeListApp In this library, the values are put by Sheets API. When the Sheets API is directly used, it might be difficult to create the request body. So I created this as a wrapper for using Sheets API. — Tanaike, Jan 30 '22 at 01:16
@Tanaike yes!! that's exactly what's I am looking for if I understood the documentation correctly, so just to clarify I can specify multiple ranges and then specify multiple values and then they get updated in batch and NOT sequential right? also can you add this as an answer? — , Jan 30 '22 at 03:29
Thank you for replying. I thought that your understanding is correct. So as a simple sample script, I introduce a sample script. Could you please confirm it? If that was not useful, I apologize. — Tanaike, Jan 30 '22 at 04:29

score 2 · Accepted Answer · answered Jan 30 '22 at 04:29

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:

Impressive! Indeed. Upvoted ... can I suggest a slight modification? — Mike Steelson, Jan 30 '22 at 09:36
@Mike Steelson Thank you for your comment. I had wished that Class RangeList was updated for directly achieving this situation. So, as the current workaround, I created such a library. — Tanaike, Jan 30 '22 at 12:09

Mike Steelson · Answer 2 · 2022-01-31T08:37:05.387

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)

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)

2 Answers2

1. Install library.

2. Sample script:

References: