Reducing Google Apps Script execution time with using an array?

Question

I wrote a script to periodically copy data from one column depending on if each cell was determined to have current data (Designated as ALIVE in another column), and place that data in another column in a different sheet. The script doesn't exceed the execution time, however I was wondering if there was a way to make it faster by utilizing Arrays.

I appreciate the help, I'm new to Google Apps Script programming but plugging along. Many thanks in advance for the advice.

function copyFunctionDATA() {

  var defSheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("(DATA)")
  var defSheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("(DATAdead)")
  var numLastRow = 60

for (var x=11; x<=numLastRow; x++) {

  var srcRange = defSheet1.getRange(x,1);
  var srcRange2 = defSheet1.getRange(x,1);
  var value = srcRange.getValue();  
  var value2 = srcRange2.getValue();

if (value2.indexOf("ALIVE") !== -1) {
   defSheet2.getRange(x,1).setValue(value);
  }
 }}

Serge insas · Accepted Answer · 2013-07-15 19:05:07Z

1

Transposing in 2D array is very simple. The main difference is the way data is indexed : ranges count from 1 and arrays count from 0.

So to transpose your code you should get 2 arrays (one for each sheet) and iterate the corresponding cells, change the value depending on your condition and write back the array to the spreadsheet to update it.

Here is a rough transpose of your code with a couple of comments to explain : (some variables ought to be renamed for clarity)

function copyFunctionDATA() {

  var defSheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("(DATA)").getDataRange().getValues();// read the whole sheet in a 2D array
  var defSheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("(DATAdead)").getDataRange().getValues();// read the whole sheet in a 2D array
  var numLastRow = 59 ; // I suppose you intentionally limit to the 60 first rows ?

for (var x=10; x<=numLastRow; x++) {  // starting from row 11 >> becomes 10 


  var value = defSheet1[x][0]; 
  var value2 = defSheet1[x][0]; // you made a mistake in your code : you define 2 identical ranges !! change it to your need  : 0 is column A, 1 is B etc...

if (value2.indexOf("ALIVE") !== -1) {
   defSheet2[x][0] = defSheet1[x][0]; 
  }
 }
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("(DATAdead)").getRange(1,1,defSheet2.length,defSheet2[0].length).setValues(defSheet2);// write back defSheet2 array to sheet (DATAdead)
}

EDIT : if you want to overwrite only the first column in defSheet2 change simply the range definition for this sheet, for example like this :

  var defSheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("(DATAdead)").getRange('A1:A').getValues();// read the whole sheet in a 2D array

edited Jul 15, 2013 at 19:05

answered Jul 11, 2013 at 22:26

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

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

7 Comments

HoosierTIM Over a year ago

Serge, many thanks for the code and explanations... it's appreciated because the concept now makes sense, and I can follow logically. Yes I made a mistake in the code, it should have been column AN or 39 in this case. When running the script though, I am getting "TypeError: Cannot read property "0" from undefined." Line 11. I've been trying to solve it myself before coming back to ask, but it gets stuck at defSheet1[x][0]. All the best.

Serge insas Over a year ago

Oooops sorry, I forgot to add’.getValues()’ at the end of 2 lines... (they where so long that I didn't see it ;-) code now updated (see defSheet 1 & 2)

HoosierTIM Over a year ago

Serge, many thanks... works beautifully. I didn't think to go back to defSheet 1 &2. Only thing is that it's writing data to DATAdead on all columns, not just column A. That being said, I should be able to figure it out from and I'm in your debt. All the best.

Serge insas Over a year ago

In fact this was intentional... since defSheet2 array is a mirror of the whole sheet (except for the modified cells) it is safe to write back the whole array to the sheet. btw, please consider accepting the answer, thanks

HoosierTIM Over a year ago

Serge, the issue is I have some formulas in other columns on that sheet that get removed. I can move them elsewhere, but that's the reason why I was looking to narrow it down to just column A. That said, this has been a huge help.

|

Collectives™ on Stack Overflow

Reducing Google Apps Script execution time with using an array?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related