Using setvalues with a for loop

Question

I am currently trying to perform a "copyTo" or "setValues" action. I need to copy one grid (Sheet1!J16:Q36) to another grid (Sheet2!J16:Q36) But not every cell shall be copied. Only those values that are not identical to the Sheet1 values shall be copied.

I have tried the below code with success, but sadly the script takes ages. I understand that a batch operation with getValues in an array will be quicker, but I lack the capability to do that script.

I also used a third grid which compared the values of sheet1 and 2 and returned 1 or 0. Only if the value 1 was shown, the cell was considered by the for loop. I take it that this is inefficient.

Thank you for your help. I appreciate it a lot.

 var ratenprogramm = SpreadsheetApp.getActiveSpreadsheet(); 
 var ratenprogrammmain = ratenprogramm.getSheetByName("Ratenprogramm");
 var vorlageratenprogramm = 
 ratenprogramm.getSheetByName("VorlageRatenprogramm");

for(i=1;i<=21;i++)
{
  for(j=1;j<=8;j++)
  {
    if(vorlageratenprogramm.getRange(37+i,9+j).getValue() == 1)
    {
      vorlageratenprogramm.getRange(15+i,9+j).copyTo(ratenprogrammmain.getRange(15+i,9+j),{contentsOnly: true}); 
    }

  }
}

Please include your whole script, we can't tell what vorlageratenprogramm or ratenprogrammmain are defined as. — ross
Hi Ross, sorry, I forgot. Those are just the sheet names.I put them in the text above. — Kristian

ziganotschka ziganotschka · Accepted Answer · 2019-10-23T08:13:26

As you have noticed, calling any external services, including methods like getValue() make your script slow, see Apps Script Best Practices.
Your code can be optimized by replacing the multiple getValue() requests by a single getValues().
Within the nested loops you can specify a multiple amount of ranges and values that can be written with the Advanced Sheets Service, with the Sheets API method spreadsheets.values.batchUpdate into the corresponding ranges of the destination sheet, see also here.

Sample

function myFunction() {

 var ratenprogramm = SpreadsheetApp.getActiveSpreadsheet(); 
 var ratenprogrammmain = ratenprogramm.getSheetByName("Ratenprogramm");
 var vorlageratenprogramm = ratenprogramm.getSheetByName("VorlageRatenprogramm");
 var data=[];
 var range=vorlageratenprogramm.getRange(15,9,21,8);
 var values=range.getValues();
 for(i=0;i<4;i++)
  {
    for(j=0;j<1;j++)
    {
      if(values[i+1][j] == 1)
       {
        var cell=range.getCell(i+1,j+1).getA1Notation();
        data.push([{ range:'Ratenprogramm!'+ cell, values: [[values[i+1][j]]]}]);
      }
    }
  }
  var resource = {
    valueInputOption: "USER_ENTERED",
    data: data
  };
  Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId);
}

Keep in mind that if you have many different ranges, it might be easier and faster to overwrite the sheet with the complete range, rather than using nesting looping. E.g. vorlageratenprogramm.getRange(15,9,21,8).copyTo(ratenprogrammmain.getRange(15,9,21,8),{contentsOnly: true});.

Using setvalues with a for loop

1 Answers

Sample