AStewart AStewart - 2 months ago 14
Javascript Question

Google Sheets, For Loop to auto update stocks from google api

As the title describes I'm trying to update a stock price with a script. I understand that this is likely trivial for most of you, but it's my first time scripting with google sheets.

From what I can see this code should be okay and the code within the loop does work on it's own.

It makes a list for for every time the list has a value it will process the command. Currently the command will only repeat on the same row as I've not figured how to increment the row properly yet.

I want to read from column A and as long as there is a value to increment which A row is read to get the stock symbol, pull the value, and paste it in an incremented E row.




function getData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var symbol = ss.getRange("A2:A").getValues();

for (var row = 2, var count = symbol.length; row < count; row++) {
if (symbol[row] !=''){
ss.getRange('E2').setValue('=GOOGLEFINANCE(A2)');
}
}
}

Answer

I think this will get you what you want. The issue with your script was that the E2 was hardcoded in, as was the A2 so it didn't iterate through as needed.

function getData() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var symbol = sheet.getRange("A2:A").getValues();  

  for(i=0; i<symbol.length; i++)
  {
    if (symbol[i]!=""){
      ss.getRange('E'+(i+2)).setValue('=GOOGLEFINANCE(A'+(i+2)+')'); 
    }
  }
}