0

I have a timed trigger that runs every 15 minutes. A simplified partial version of the script is shown below. The script compiles data from about 50 other spreadsheets and records a row for each spreadsheet, then writes that summary data to the active spreadsheet.

I noticed that in the logs, there is an alternating pattern in the execution times for this script: half the executions take 200-400 seconds, and the other half typically take 700-900 seconds. It's a pretty significant difference, and the pattern persists over the past several days of logs.

There's nothing in the script itself that changes from one execution to the next, so I'm curious if anyone can suggest a reason this would happen (even better if it's a documented reason). For example, is there some sort of caching of the spreadsheet reads so that the next execution gets those values faster?

Logs of execution times

// The triggered function.
function updateRankings()
{
  var rankingSheet = SS.getSheetByName(RANKING_SHEET_NAME) // SS is the active spreadsheet
  // Read the id's of the target spreadsheets, which are stored on an external spreadsheet
  var gyms = getRowsData( SpreadsheetApp.openById(ADMIN_PANEL_ID).getSheetByName(ADMIN_PANEL_SHEET_NAME))

  // Iterate over gyms
  gyms.forEach(getGymStats)

  // Write the compiled data back to the active sheet
  setRowsData(rankingSheet, gyms)
}

function getGymStats(gym)
{
  var gymSpreadsheet = SpreadsheetApp.openById(gym.spreadsheetId)
  // Force spreadsheet formulas to calculate before reading values
  SpreadsheetApp.flush()
  var metricsSheet = gymSpreadsheet.getSheetByName('Detailed Metrics')
  var statsColumn = metricsSheet.getRange('E:E').getValues()
  var roasColumn = metricsSheet.getRange('J:J').getValues()

  // Get stats
  var gymStats = {
    facebookAdSpend: getFacebookAdSpend(gymSpreadsheet),
    scheduling: statsColumn[8][0],
    showup: statsColumn[9][0],
    closing: statsColumn[10][0],
    costPerLead: statsColumn[25][0],
    costPerAppointment: statsColumn[26][0],
    costPerShow: statsColumn[27][0],
    costPerAcquisition: statsColumn[28][0],
    leadCount: statsColumn[13][0],
    frontEndRoas: (roasColumn[21][0] / statsColumn[5][0]) || 0,
    totalRoas: (roasColumn[35][0] / statsColumn[5][0]) || 0,
    totalProjectedRoas: (roasColumn[36][0] / statsColumn[5][0]) || 0,
    conversionRate: (gym.currency ?
      '=IFS(ISBLANK(INDIRECT("R[0]C[-4]", FALSE)),,ISBLANK(INDIRECT("R[0]C[-2]", FALSE)), 1,TRUE, IFERROR(GOOGLEFINANCE("Currency:"&INDIRECT("R[0]C[-2]", FALSE)&"USD")))' :
      1)
  }
  Object.assign(gym, gymStats)
}

function getFacebookAdSpend(spreadsheet)
{
  var range = spreadsheet.getRangeByName('FacebookAdSpend')
  if (!range) return ''
  return range.getValue()
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Aaron Dunigan AtLee
  • 1,860
  • 7
  • 18
  • 1
    Have you already checked the [Best Practices](https://developers.google.com/apps-script/guides/support/best-practices)? Even though App Script is pretty fast, the speed of the SpreadsheetApp API tends to vary widely, it may run faster or slower (e.q. API being called multiple times), so it could be a possible reason of an inconsistent execution duration. However, I haven't seen any official documentations that specifically discuss this. But you may also check this existing answer for more info about when [Google APIs response time](https://stackoverflow.com/a/66971841) is being affected. – SputnikDrunk2 Feb 18 '22 at 23:55
  • Personally I would expect variation on a server shared with so many users and if this variation could affect my data calculations then I'd look for another approach. – Cooper Feb 19 '22 at 01:23
  • The code is uncomplete as several variables have not been declared (`SS`, `setRowsData`, etc.) . Related https://stackoverflow.com/q/48102206/1595451 – Rubén Feb 19 '22 at 21:20
  • Another related https://stackoverflow.com/q/56318975/1595451 – Rubén Feb 19 '22 at 21:25

0 Answers0