0
212
ViewsUpdating rows in Google Sheets using Google script
I have the following Google Sheet with a tab called db:
In reality, the list is much longer. Eventually, I want to have a function triggered every hour to download data for 50 companies from Yahoo! Finance, as to not get rate restricted.
I am currently developing this function, and have the following in Code.gs:
function trigger() {
var db = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('db');
var tickers = db.getRange('A2:A').getValues();
for (var row = 1; row <= 5; row++) {
console.log(tickers[row])
var data = yahoo(tickers[row]);
db.getRange(row, 2, 1, 3).setValues(data);
}
}
I have been messing around with different starting values for row, but just do not get the result as expected. Instead I get:
How can I:
- not overwrite the cells in
B1, C1, D1but have the data be inserted behind the respective tickers? - run the loop not 5 times (as is now hardcoded), but only as many times as there are tickers? I tried
row <= tickers.length()but got an error.
2 answers
Answer question0
Try
function trigger() {
var db = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('db');
var tickers = db.getRange('A2:A' + db.getLastRow()).getValues().flat();
for (var row = 0; row < tickers.length; row++) {
console.log(tickers[row])
var data = yahoo(tickers[row]);
db.getRange(row + 2, 2, 1, 3).setValues(data);
}
}
explanation: the index of an array (tickers) starts at 0, not 1, so, as we catch the tickers of line 2, you must add 2 to get the corresponding ticker.
0
When using the getRange(row, column, numRows, numColumns) method, keep in mind that the number 1 corresponds to row 1, so if you want to no ovewrite the headers, you must initialize it at 2.
It is also important to mention, that the method you are using is highly inefficient, since you are calling the service every time the loop iterates. If you are going to make this script call every hour, you may exceed the quotas. So I recommend that you use setValues(values).
Here is some pseudo-code that may help you.
const sS = SpreadsheetApp.getActiveSheet()
function fillWithoutLoops() {
const tickers = sS.getRange('A2:A').getValues().flat().filter(n => n)
const tickersParsed = tickers.map(yahoo)
sS
.getRange(2, 1, tickersParsed.length, tickersParsed[0].length)
.setValues(tickersParsed)
}
function yahoo(ticker) {
return [ticker, "SOME", "DATA", "MORE"]
}