CODEWITHSUNDEEP
arraysfor-loopgoogle-apps-script
Kristian
Kristian

Reputation: 83

Using setvalues with a for loop

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}); 
    }

  }
}

Upvotes: 0

Views: 182

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

  • 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});.

Upvotes: 1

Related Questions