Sheet Names stored in array, Google Apps Script only Accessing first two

Question

I have been using Google Apps Script along with spreadsheet to create a spreadsheet that watches a set of stocks. I have assigned each stock it's own sheet and set up the function with a day trigger so that it refreshes all of the information each day. I spent so much time debugging a lot and finally got it working perfectly for the first two sheets. I now added 3 more and it's not doing anything for them.

function XMLDATAONDAY() {

    for (r=0;r<5;r++){
        var ss=SpreadsheetApp.getActiveSpreadsheet()
        var sheets= ["HSY","AAPL","CENX","MSFT","TSLA"]
        var sheet=ss.getSheetByName(sheets[r])
        var i=14
        var dateSrc=sheet.getRange(2,5) 
        var stockPrice = sheet.getRange(5,4).getValue()
        var displayCell= sheet.getRange(2,4)
        var date = dateSrc.getValue()

        SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1IOBQpdUr0fq_clie-0AOnCAN87qTy9Yn1h79akMJ7uc/edit#gid=0');
        for (i=14;i<366;i++) {
            var sheets= ["HSY","AAPL","CENX","MSFT","TSLA"]
            var stockCell=sheet.getRange(i,2)
            var dateCell=sheet.getRange(i,1)

            if(stockCell.getValue()== ""){
                sheet.getRange(14,1).copyFormatToRange(sheet, 1, 1, i, i)
                sheet.getRange(14,2).copyFormatToRange(sheet, 2, 2, i, i)
                dateCell.setValue(date);
                stockCell.setValue(stockPrice);
                i=400;
            }
        }
    }
}

It's probably something that I'm just overlooking, but I just can't seem to find anything.

Answer 1

This line:

i=400;

Should probably be changed to:

break;

I'm guessing that you are setting i to 400 in order to stop the next loop from running?

There is a duplicate variable assignment for sheets. It's an array literal. Only define that once, and put it outside of the for loops. Put the variable assignment for ss outside of the loop also. If you do not assign r with a statement, the code still runs without any noticeable difference, but without a var statement, the variable is defined in the global scope.

The statement to open a spreadsheet by URL is not doing anything. It's not assigned to a variable, so I'm not sure what that line is for.

function XMLDATAONDAY() {
  var date,dateCell,dateSrc,displayCell,i,r,sheet,sheets,ss,stockCell,stockPrice;

  sheets = ["HSY","AAPL","CENX","MSFT","TSLA"];
  ss=SpreadsheetApp.getActiveSpreadsheet();
  r=0;
  
  for (r=0;r<5;r++){
    sheet=ss.getSheetByName(sheets[r])
    i=14
    dateSrc=sheet.getRange(2,5) 
    stockPrice = sheet.getRange(5,4).getValue()
    displayCell = sheet.getRange(2,4)
    date = dateSrc.getValue()
 
    //SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/sheet_ID_HERE/edit#gid=0');
    for (i=14;i<366;i++) {
      stockCell=sheet.getRange(i,2)
      dateCell=sheet.getRange(i,1)
     
      if(stockCell.getValue()== ""){
        sheet.getRange(14,1).copyFormatToRange(sheet, 1, 1, i, i)
        sheet.getRange(14,2).copyFormatToRange(sheet, 2, 2, i, i)
        dateCell.setValue(date);
        stockCell.setValue(stockPrice);
        break;
      }
    }
  }
}