Syncing between Spreadsheets

Question

Currently have a working script for syncing cell format and values to another spreadsheet. I know it is very rough and ready, but it works!! I don't like that there are numbers in the For loop to achieve the correct cell numbers ... But this is needed to go from a single row by 9 columns, to the destination spreadsheet which is 9 rows by 1 column.

After talking to a colleague, the feedback is this will run quite slowly. I have rewritten this as 9 If statements but the problem is when dragging cells across it only updates the first cell, not the range.

There is the copyTo function, unfortunately, this only works inside a single spreadsheet.

The overall speed doesn't really matter, is there an alternative?

// for loop to populate all cells
  for (let i = 21; i < 30; i++){                                              // starts at volumn V (22) for 9 columns (21 to 30)
  var activeCell = registerSheet.getRange(activeRow, i + 1, 1, 1);      // Register column, i + 1 so it increases
  var registerValue = activeCell.getValue();                            // get cell value
  var registerFormat = activeCell.getBackground();                      // get cell format     
  var i2 = 'C' + (33 + i)                                               // this increases the target cell range from C54 to C62 using the loop. 54 (start row on form) - 21 (start i value) = 33
  var range = form.getRange(i2);                                        // designate target cell
  range.setValue(registerValue);                                        // set target cell value
  range.setBackground(registerFormat);                                  // set target cell format 
  }
  }

Hi Marios. Yes it worked perfectly. I wanted to thank you, but the instructions below say not to reply with 'thanks'!. — Will Griffin, Oct 23 '20 at 07:38

Marios · Accepted Answer · 2020-10-20T10:56:04.243

Explanation:

Your code is indeed inefficient because you are calling setValue() and setBackground() 9 times each when you can simply use setValues() and setBackgrounds() instead once. It will get even more inefficient for a larger number of iterations.
There is a little trick you need to do and that is to convert the data (nrows,ncolumns) returned by getRange(activeRow,22,1,9).getValues() with a shape of (1,9) to a shape of (9,1) and do the same for the background colors as well:
```
var rowValues=registerSheet.getRange(activeRow,22,1,9).getValues().flat().map(r=>[r]); // V:AD
var rowBackcolors = registerSheet.getRange(activeRow,22,1,9).getBackgrounds().flat().map(r=>[r]); // V:AD
```

After that you can simply set the data directly to the destination sheet:

var range = form.getRange('C54:C62'); // C54:C63
range.setValues(rowValues);
range.setBackgrounds(rowBackcolors);

Solution:

Replace the for loop with this:

var rowValues=registerSheet.getRange(activeRow,22,1,9).getValues().flat().map(r=>[r]); // V:AD
var rowBackcolors = registerSheet.getRange(activeRow,22,1,9).getBackgrounds().flat().map(r=>[r]); // V:AD 
var range = form.getRange('C54:C62'); // C54:C63
range.setValues(rowValues);
range.setBackgrounds(rowBackcolors);

Syncing between Spreadsheets

1 Answers1

Explanation:

Solution:

Related article: