0

I have an array containing 2k elements, gotten from a column in google sheets. Each element is to be set/repeated 105 times in a different sheet and next to the first occurrence of that element, I'll set a formula that will populate adjacent columns with more data.

The code below works, but it takes too long and I was wondering if there'd be a more effecient solution.

function getGFTickersData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LIST OF STOCKS");
  var tickerRng = ss.getRange(2, 1, ss.getLastRow(), 1).getValues();
  var TDSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TickersData");
  var startRow = 2;
  var tickerArr = [];
  for (var b = 0; b < tickerRng.length; b++) {
    var tickerToArr = tickerRng[b][0];
    tickerArr.push(tickerToArr);
  }
  for (var a = 0; a < tickerArr.length; a++) {
    var ticker = tickerArr[a];
    if (tickerArr.indexOf(ticker) != -1) { //So it writes the tickers once
      if (ticker[0] != '') {
        var row = TDSheet.getRange(TDSheet.getLastRow() + 1, 1, 105, 1).setValue(ticker);
        var targetRow = TDSheet.getRange("B:B").getValues();
        var maxIndex = targetRow.reduce(function (maxIndex, row, index) {
          return row[0] === "" ? maxIndex : index;
        }, 0);
        var row = TDSheet.getRange(maxIndex + 2, 2).setFormula("=query(googlefinance(" + '"' + ticker + '"' + ",'all shares'!A4,'all shares'!D3,'all shares'!D4,'all shares'!D5)," + '"' + "select *" + '"' + ",1)");
      }
    }
  }
}

It usually gives me an Exceeded maximum execution time error.

Appreciate your help in pointing me to a better way to build it.

onit
  • 2,275
  • 11
  • 25

1 Answers1

3

In short, always minimise API calls.

In your case, avoid calling setValue() and getValues() inside the for-loop.

Instead, for example, populate an array and then call the relevant API method after the for loop:

E.g.

function myFunc() {
/* 
snip
*/

  var rows = [];
  for (var a = 0; a < tickerArr.length; a++) {
    rows.push([tickerArr[a]]);
  }
  TDSheet.getRange(TDSheet.getLastRow() + 1, 1, 105, 1).setValues(rows);
}

Note that the "shape" of the array when using setValues() requires an array of rows such as [[A1,B1],[A2,B2],[A3,B3]] where A1 etc. are the usual cell addresses.

Reference:

Iamblichus
  • 18,540
  • 2
  • 11
  • 27
Paul
  • 887
  • 6
  • 22
  • Hello! Thanks for the example, but it actually throws me an error, because the nº of rows in the data does not match the nº of rows in the range (105). This is why I was setting the values in each iteration. – onit Apr 02 '21 at 23:25
  • 2
    That error won't be intrinsic to the approach but more to do with the details of your code. You need to set the number of rows in setValues() to be equal to the array you're reading in (which from your code is tickerArr.length). If you want the same value for all 105 cells, then push the same value 105 times into the array and then use it with setValues() after the for-loop. – Paul Apr 02 '21 at 23:30