How to write array to sheets column and add custom formula to column next to it using google apps script?

Question

Here's the problem:

Get a list of tickers from a column in a sheet (LIST OF STOCKS)

Push it, so as each ticker appears in the array 105 times

Write the resulting array to another sheet's column (TickersData)

Next to the first ticker each element brought to TickersData, add the formula.

Here's the code, where I could get up to repeating the values within an array, but can't write them to the other sheet:

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];
    if (tickerRng[b] != '') {
      var repeated = [].concat(... new Array(104).fill([tickerToArr]));
      tickerArr.push(repeated);
    }
  }
  Logger.log(tickerArr.length);

  TDSheet.getRange(TDSheet.getLastRow() + 1, 1, tickerArr.length, 1).setValues(tickerArr);

  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(" + '"' + tickerArr + '"' + ",'all shares'!A4,'all shares'!D3,'all shares'!D4,'all shares'!D5)," + '"' + "select *" + '"' + ",1)");
}

Here's an image of one ticker as an example. After 105 rows, there should be a new ticker in column A and the formula added next to it in column B:

Answer 1

I believe your goal as follows.

• You want to retrieve the values from "A2:A" from "LIST OF STOCKS" sheet.
• You want to put the retrieved value to "Sheet10" sheet by including 105 rows that 1st row has the formula of =query(googlefinance("###",'all shares'!A4,'all shares'!D3,'all shares'!D4,'all shares'!D5),"select *",1).

In order to achieve your goal, I would like to propose the following sample script.

Sample script:

function getGFTickersData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LIST OF STOCKS");
  var tickerRng = ss.getRange(2, 1, ss.getLastRow() - 1, 1).getValues();
  var TDSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet10");
  var values = tickerRng.flatMap(([a]) => [[a, `=query(googlefinance("${a}",'all shares'!A4,'all shares'!D3,'all shares'!D4,'all shares'!D5),"select *",1)`], ...new Array(104).fill([a, ""])]);
  TDSheet.getRange(TDSheet.getLastRow() + 1, 1, values.length, 2).setValues(values);
}

Reference:

• setValues(values)