Using setValue on a Large, Non-Linear Cell Range in Google Sheets Script

0
votes

I have a Google Sheets script that runs onEdit of a specific cell, and it sets the value of 100+ cells, that are not in a linear range. I am using an individual setValue command for each cell, which is resulting in the function running extremely slowly (each cell takes just under a second to populate!).

I am guessing there is a more efficient way to accomplish this, and I'd love to hear any suggestions.

Here is an excerpt of my code (I've only included a small handful of the 100+ setValue commands):

  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var dateselected = ss.getRange ("Data Sheet!A2").getValue();
  var daterow = getdaterow(dateselected); //the row of the selected date
    ss.getRange("Reconciliation!G3").setValue(ss.getRange("Data Sheet!D" + daterow).getValue());
    ss.getRange("Reconciliation!K3").setValue(ss.getRange("Data Sheet!E" + daterow).getValue());
    ss.getRange("Reconciliation!E7").setValue(ss.getRange("Data Sheet!F" + daterow).getValue());
    ss.getRange("Reconciliation!G7").setValue(ss.getRange("Data Sheet!G" + daterow).getValue());
    ss.getRange("Reconciliation!E8").setValue(ss.getRange("Data Sheet!H" + daterow).getValue());
    ss.getRange("Reconciliation!G8").setValue(ss.getRange("Data Sheet!I" + daterow).getValue());

For context: a specified date is stored in Data Sheet!A2, which is used in function getdaterow to determine the row number to be used in setting the values.

Thankyou in advance!

1
javascriptgoogle-sheetsspreadsheetsetvaluegoogle-apps-script-editor

1 Answers

0
votes
  • You want to copy the value of one cell to one cell using Google Apps Script.
  • There are a lot of ranges for copying in your situation.
  • You want to reduce the process cost of your script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

In this answer, I use 2 methods of spreadsheets.values.batchGet and spreadsheets.values.batchUpdate in Sheets API for reducing the process cost. In this case, your goal can be achieved by 2 API calls.

Modified script:

Please modify your script as follows. In this case, your current script in your question is modified. So when you use this at the actual situation, please modify object for your situation. And also, before you run the script, please enable Sheets API at Advanced Google services.

From:

var ss = SpreadsheetApp.getActiveSpreadsheet ();
var dateselected = ss.getRange ("Data Sheet!A2").getValue();
var daterow = getdaterow(dateselected); //the row of the selected date
ss.getRange("Reconciliation!G3").setValue(ss.getRange("Data Sheet!D" + daterow).getValue());
ss.getRange("Reconciliation!K3").setValue(ss.getRange("Data Sheet!E" + daterow).getValue());
ss.getRange("Reconciliation!E7").setValue(ss.getRange("Data Sheet!F" + daterow).getValue());
ss.getRange("Reconciliation!G7").setValue(ss.getRange("Data Sheet!G" + daterow).getValue());
ss.getRange("Reconciliation!E8").setValue(ss.getRange("Data Sheet!H" + daterow).getValue());
ss.getRange("Reconciliation!G8").setValue(ss.getRange("Data Sheet!I" + daterow).getValue());

To:

var ss = SpreadsheetApp.getActiveSpreadsheet ();
var dateselected = ss.getRange ("Data Sheet!A2").getValue();
var daterow = getdaterow(dateselected); //the row of the selected date

// I modified below script.
var object = [
  {srcRange: "Data Sheet!D" + daterow, dstRange: "Reconciliation!G3"},
  {srcRange: "Data Sheet!E" + daterow, dstRange: "Reconciliation!K3"},
  {srcRange: "Data Sheet!F" + daterow, dstRange: "Reconciliation!E7"},
  {srcRange: "Data Sheet!G" + daterow, dstRange: "Reconciliation!G7"},
  {srcRange: "Data Sheet!H" + daterow, dstRange: "Reconciliation!E8"},
  {srcRange: "Data Sheet!I" + daterow, dstRange: "Reconciliation!G8"},
];
var spreadsheetId = ss.getId();
var srcRanges = {ranges: object.map(function(e) {return e.srcRange})};
var srcValues = Sheets.Spreadsheets.Values.batchGet(spreadsheetId, srcRanges);
var resourceForBatchUpdate = {valueInputOption: "USER_ENTERED", data: object.map(function(e, i) {return {range: e.dstRange, values: srcValues.valueRanges[i].values}})};
Sheets.Spreadsheets.Values.batchUpdate(resourceForBatchUpdate, spreadsheetId);
  • At object, the source range and destination range are set to an array as an object. For example, ss.getRange("Reconciliation!G3").setValue(ss.getRange("Data Sheet!D" + daterow).getValue()); is {srcRange: "Data Sheet!D" + daterow, dstRange: "Reconciliation!G3"}.
    • Please modify object for your actual situation like this.
  • This modified script supposes that daterow returns the row number.

References:

If I misunderstood your question and this was not the direction you want, I apologize.