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
– ross, Commented Oct 23, 2019 at 7:41
Hi Ross, sorry, I forgot. Those are just the sheet names.I put them in the text above. — Kristian
– Kristian, Commented Oct 23, 2019 at 7:45

ziganotschka · Accepted Answer · 2019-10-24 08:02:13Z

1

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

edited Oct 24, 2019 at 8:02

answered Oct 23, 2019 at 8:13

ziganotschka

26.9k2 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ross Over a year ago

Note: you will need to manually enable the Sheets API for this script to work.

Kristian Over a year ago

Thank you very much Ziganotschka and Ross for your help. I have two questions on your code: - what line would I need to add to get the "data" input in a grid of my choice? (say "ratenprogramm.getRange(16,9,21,8)). - Your second example with the copyTo: This would work perfect. But I also have some empty cells within the grid that shall not be copied. Is there a way to simply ignore empty cells and copy the rest? Thank you so much. :)

ziganotschka Over a year ago

The way to ignore empty cells is not simple, it would probably be simpler to posteriorly delete the cells that should remain empty, after copying the whole range. Otherwise, you need to select all ranges to be copied as you have done with if...getValue... (slow) or (a bit faster) as in my sample with batchUpdate. To obtain data in the range (16,9,21,8), replace var range=vorlageratenprogramm.getRange(15,9,21,8); by var range=vorlageratenprogramm.getRange(16,9,21,8);

Kristian Over a year ago

I am still struggling to get this to work sadly. I can not figure out how to make the batchUpdate work. I can not figure out where the filled array (var data) shall go within the code. Could you kindly inform where I shall place the setValues(data) part exactly? Currently I am also getting an error which states something like (property of "0.0" of undefined can not be read). Thank you again for your valuable time.

ziganotschka Over a year ago

If you use batchUpdate, there is no setValues(). Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId); sets the values specified in data into the range specified in data. The only thing: If you want to set the values a different sheet, range should be defined as: range:'Ratenprogramm!'+ cell`, I updated my answer with the complete code.

|

Collectives™ on Stack Overflow

Using setvalues with a for loop

1 Answer 1

Sample

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related