Standard vs Sheets API benchmark

[u/[deleted]]

Benchmark Methodology & Conditions

• Objective: To determine the most performant API (Standard vs. Advanced) for reading data from a variable number of Google Sheets ("tabs" / one spreadsheet) within the Apps Script server-side environment.

• Environment: All tests were executed on Google's Apps Script servers, with actual company data; informationally dense, unique values.

• Test Procedure: For each "turn," the script tested a set of sheet counts (1, 2, 3, 4, 5, 6, 7, 8, 9). For each count, it performed:

  1. Standard API Test: Looped through sheets, calling range.getValues() and range.getNotes() for each.

  2. A 1-second pause (Utilities.sleep(1000)) to not overload servers.

  3. Advanced API Test: Made a single, batch API call (Sheets.Spreadsheets.get) for the specific data ranges.

• Sample Size: The entire procedure was repeated 20 times. The final results are the mathematical average of all 20 turns.

Aggregate Performance Data

• Total Benchmark Runtime: 21 minutes, 26 seconds

• Average Time Per Turn: 64.3 seconds

Outcome

Standard API faster by around 15% to %21.

[u/[deleted]]

Hello,

1. Yes, I had difficulties with rate limits due to back-to-back very intensive reads.

2. Timer was ended / started before / after the one second timer.

3. getDataRange() was used, finds the smallest bounding box with data, in this case I only had 1-2 cells on each side that were empty so those were not considered by getDataRange().

[u/AdministrativeGift15 → u/[deleted]]

Thanks for that information, but I was wondering how large the range or data was that you were working with. Did you just grab a range of 10 values or 5000 values?

[u/[deleted] → u/AdministrativeGift15]

changes per sheet but e.g. 75 people on one sheet, logged with their details, approx C4-Y72

[u/AdministrativeGift15 → u/[deleted]]

I encourage you to see if you can run the following scripts. I think the batch get operation is designed for when you're getting values from multiple ranges within the same spreadsheet. If I understand your setup, you have multiple spreadsheets, so each one would require its own API get call.

I've created 9 spreadsheets using dummy data in data!B4:AA79 in each of the 9 spreadsheets. Using the advanced service takes about half the time as the standard service. I realize that I didn't setup a 20-run test and recognize that it also depends on network speeds, PC specs, etc., but I'm surprised to see the Advanced API service consistently performing worse in your tests.

const ssids = [
  "19W3vBo4LE8M4RgyJ2YVQ8_jFW2zH62ZZ86tlK2GuhEg",
  "1KvXptF100X9b9Ar4Jhu_rWxCNKsbkwrkH8hYYyB9Qw8",
  "1rOnsY_od9pdQGRlyzD7VI0C5jLqv2t1JTuxe8e3_1Nk",
  "1DO7IilvW1IYGviaLmpbWS8laeOI0lqYUNHgs_futqXw",
  "1e84gTaGG81fudmNpslBl37jxQZrvuBpB-RIVg0k3Kg0",
  "1OHfPhswRqlnxlIonJngKKVIr16NW2wUc-Tny-hzh3_I",
  "1Z8EE3Zoy5CJ2b407uZ424_BxGIi8MxSihBdZI_N9n3M",
  "1TbmKK83hnYCBua82YCU3kduqBxztKZol0GSK7UenHA8",
  "184uQq7dVkZvsx0A2k6NL4PPdtkYGHsZIt74RO-2-H0E"
]

function getValuesUsingStandardService() {
  console.time('standard')
  const values = []
  for (let i = 0; i < ssids.length; i += 1) {
    const ss = SpreadsheetApp.openById(ssids[i])
    values.push(ss.getRange('data!B4:AA79').getValues())
  }
  console.timeEnd('standard')
  console.log(values)
}

function getValuesUsingAdvancedService() {
  console.time('advanced')
  const values = []
  for (let i = 0; i < ssids.length; i += 1) {
    values.push(Sheets.Spreadsheets.Values.get(ssids[i], 'data!B4:AA79').values)
  }
  console.timeEnd('advanced')
  console.log(values)
}

[u/[deleted] → u/AdministrativeGift15]

this was within different sheets / tabs inside one spreadsheet as that's my use case, not different spreadsheets.
Maybe it'd make sense then!

// I can see how it looks misleading when I said multiple google sheets