Using input data as Google Sheets Spreadsheet or Worksheet name

I ended up not using Zapier due to the cost being unreasonable for a personal tool, but hopefully this is useful to you, or someone else.  I needed to read sheets by name, but they were all being created manually with dates as their names so I had no way to get their IDs.

I ended up making a sheet at the end of my document named “SheetReference” 

The first row had headers named “Sheet Name” and “Sheet ID”, but frankly their names don’t matter.

I went into the google sheets script system and added a function that looks like:

function getSheetInfo() {
  var values = SpreadsheetApp.getActiveSpreadsheet().getSheets().map(s => [
    s.getSheetName(), s.getSheetId()
  ])

  SpreadsheetApp.getActive()
    .getSheetByName('SheetReference')
    .getRange("A2:B" + (values.length + 1))
    .setValues(values);
}

Run it manually to get the page started, then you can setup a trigger (triggers on the left side of the apps script page) to have this run once a day so it gets updated when sheets are added/removed.

Since the ID for “SheetReference” never changes you can setup a step to read from that document and look up the ID based on the sheet name in the table.  Just use normal mechanisms to get the ID for the “SheetReference” sheet (Look in the URL after the gid= to get it - it’ll also be on your new table on that sheet, too, so you can look there after you run it once.) 

Then you can use the ID, that you retrieved, in a later google sheets step to read from the appropriate sheet in the same document.  In my case, I was able to use a formatted version of the date to use the first lookup step and get an ID, and then access the sheet that had that name in the next step.