CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Pr0no
Pr0no

Reputation: 4109

Updating rows in Google Sheets using Google script

I have the following Google Sheet with a tab called db:

enter image description here

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:

enter image description here

How can I:

Upvotes: 0

Views: 99

Answers (2)

Emel
Emel

Reputation: 2462

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"]
}

Documentation

Upvotes: 1

Mike Steelson
Mike Steelson

Reputation: 15328

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.

Upvotes: 1

Related Questions