Updating rows in Google Sheets using Google script

Question

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, D1 but 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.

Emel · Accepted Answer · 2022-05-31 11:04:51Z

1

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

Use batch operation(Best practices in Google Apps Script)

answered May 31, 2022 at 11:04

Emel

2,5041 gold badge11 silver badges23 bronze badges

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

2 Comments

Pr0no Over a year ago

Thanks! I find this difficult to inderstand (how it works exactly), will read up.

Emel Over a year ago

The main pints are: 1. Extract the values and filter it to have an array like ["BLK","AAPL", ...], 2. Pass this array to a map function, there is where your fetch logic should be 3. Get a range that matches the dimensions of the new array, we get the numRows via tickersParsed.length and the numColumns via tickersParsed[0].length 4. Finally we set the values via setValues

Mike Steelson · Accepted Answer · 2022-05-31 10:56:20Z

1

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.

answered May 31, 2022 at 10:56

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

Collectives™ on Stack Overflow

Updating rows in Google Sheets using Google script

2 Answers 2

Documentation

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Documentation

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related