Invoking Google Finance API from app script with schedule run. WEIRD RESULT

So basically, I need to use googlefinance api to fetch over 100 dow jones indicies daily price update with around 3 years of data.

Now you may ask why don’t I simply use:
=GOOGLEFINANCE("ticker", "all","START_DATE",TODAY(), "DAILY")
directly in my google sheet.Since the function is using TODAY(), it should be able to refresh and update with daily frequency.

That’s because I find out that the google sheet only returns [[”NA”]] when I connect the sheets to my google colab notebook. In short, after some testing, I learnt that the function is returning more like a preview of data rather than a snapshot of data on the google sheet.

Therefore, i come up with a workaround: to use app script to call the function and fill up the google sheet. (If you are confused here, the difference it makes is there’s no google function observed in any cells from the sheet, only the data itself)

So far the logic works for me, it allows me to update the sheet while getting its data from my colab notebook :

// Function to update a single index sheet
function updateSingleIndex(ticker, sheetName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    Logger.log("Sheet '" + sheetName + "' not found");
    return;
  }
  
  var startDate = new Date();
  startDate.setFullYear(startDate.getFullYear() - 3);  // 3 years ago
  var endDate = new Date();
  
  Logger.log("Updating " + ticker + " from " + startDate.toDateString() + " to " + endDate.toDateString());
  
  // Clear existing content
  sheet.clear();
  
  // Insert the GOOGLEFINANCE formula
  var formula = '=GOOGLEFINANCE("' + ticker + '", "all", DATE(' + startDate.getFullYear() + ',' + (startDate.getMonth()+1) + ',' + startDate.getDate() + '), DATE(' + endDate.getFullYear() + ',' + (endDate.getMonth()+1) + ',' + endDate.getDate() + '), "DAILY")';
  sheet.getRange("A1").setFormula(formula);
  
  // Wait for the formula to calculate (this is not guaranteed to be enough time)
  Utilities.sleep(10000);
  
  // Get the values
  var values = sheet.getDataRange().getValues();
  
  if (values.length > 1) {  // Check if we have data (more than just the header row)
    // Remove the formula and set the values
    sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
    // Check if the last row is today's date
    var lastRowDate = new Date(values[values.length - 1][0]);
    var today = new Date();
    if (lastRowDate.toDateString() !== today.toDateString()) {
      Logger.log(ticker + " might be missing today's data. Last date: " + lastRowDate.toDateString());
    }
    Logger.log(ticker + " updated successfully. Rows: " + values.length);
  } else {
    Logger.log("No data fetched for " + ticker);
  }
}

// Store the list of indices in a separate function for easy management
function getIndices() {
  return [
    {ticker: "pseudo", sheet: "pseudo"},
  ];
}

Now this part is getting weird,the schedule run will invoke the function to update all sheets every morning at 7am(or 8am, i modify it). It works, it updates all the sheets, but not working intentionally:

// Main function to update all indices
function updateAllIndices() {
  var indices = getIndices();
  var scriptProperties = PropertiesService.getScriptProperties();
  var lastProcessedIndex = parseInt(scriptProperties.getProperty('lastProcessedIndex') || '-1');
  
  var startTime = new Date().getTime();
  var timeLimit = 4 * 60 * 1000; // 4 minutes in milliseconds
  
  for (var i = lastProcessedIndex + 1; i < indices.length; i++) {
    if (new Date().getTime() - startTime > timeLimit) {
      // We're approaching the time limit, let's stop and schedule the next run
      scriptProperties.setProperty('lastProcessedIndex', i - 1);
      scheduleNextRun();
      return;
    }
    
    updateSingleIndex(indices[i].ticker, indices[i].sheet);
    
    // Update the last processed index after each successful update
    scriptProperties.setProperty('lastProcessedIndex', i);
  }
  
  // If we've processed all indices, reset the counter and schedule next day's run
  if (lastProcessedIndex >= indices.length - 1) {
    scriptProperties.setProperty('lastProcessedIndex', '-1');
    Logger.log("All indices have been updated.");
    scheduleNextDayRun();
  } else {
    // If we haven't finished all indices, schedule the next run
    scheduleNextRun();
  }
}

function scheduleNextRun() {
  // Delete any existing triggers for updateAllIndices
  deleteTriggers('updateAllIndices');
  
  // Schedule the next run in 1 minute
  ScriptApp.newTrigger('updateAllIndices')
    .timeBased()
    .after(60 * 1000) // 1 minute
    .create();
  
  Logger.log("Next run scheduled in 1 minute.");
}

function scheduleNextDayRun() {
  // Delete any existing triggers for updateAllIndices
  deleteTriggers('updateAllIndices');
  
  // Schedule the next run for tomorrow at 8 AM
  var tomorrow = new Date();
  tomorrow.setDate(tomorrow.getDate() + 1);
  tomorrow.setHours(8, 0, 0, 0);
  
  ScriptApp.newTrigger('updateAllIndices')
    .timeBased()
    .at(tomorrow)
    .create();
  
  Logger.log("Next run scheduled for tomorrow at 8 AM.");
}

function deleteTriggers(functionName) {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() == functionName) {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

function setupDailyTrigger() {
  // Delete any existing triggers for updateAllIndices
  deleteTriggers('updateAllIndices');
  
  // Create a new trigger to run updateAllIndices every day at 8 AM
  ScriptApp.newTrigger('updateAllIndices')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
  
  Logger.log("Daily trigger set to run updateAllIndices at 8 AM.");
}

Note that my timezone is different, so i actually was expecting to get Tue Sep 24 data as the latest update,the market should close at around 5am at my timezone. Now, looking at the log:

Sep 25, 2024, 7:38:41 AM Info Updating INDEXDJX:DJUSNS from Sat Sep 25 2021 to Wed Sep 25 2024 Sep 25, 2024, 7:38:57 AM Info INDEXDJX:DJUSNS might be missing today's data. Last date: Mon Sep 23 2024

It actually only download the data from previous day. Normally I would question whether my getallindicies() function is bugging out for me. However, when I manually execute the function, it somehow works again:

Sep 25, 2024, 10:10:15 AM Info Updating INDEXDJX:DJUSNS from Sat Sep 25 2021 to Wed Sep 25 2024 Sep 25, 2024, 10:10:26 AM Info INDEXDJX:DJUSNS might be missing today's data. Last date: Tue Sep 24 2024 Sep 25, 2024, 10:10:26 AM Info INDEXDJX:DJUSNS updated successfully. Rows: 754

Am i missing something, here? Why does my manual execution works while automatic schedule doesn’t?
Is it only because of the time delay?
Also, one minor thing is that I set it to run at 7am before(YES, i know my code shows 8am, but that was after modification), the schedule run starts only at 7:38AM, which seems like a huge discrepancy.