How to obtain SpreadsheetID and/or sheet name as a parameter?

Hi,
For various reasons (among other for integrating with Integromat), it would be very useful to be able to obtain the spreadsheetID and/or the sheet name as parameters. This is especially useful when backing up and creating a copy of the spreadsheet. It will ensure Integromat is always referring to the relevant Spreadsheet.
Thanks

I’m not sure about obtaining the spread sheet ID but for the purpose of creating a copy/ backup, here is a simple script courtesy of @Jeff_Hager

function makeCopy() {

// generates the timestamp and stores in variable formattedDate as year-month-date hour-minute-second
var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");

// gets the name of the original file and appends the word "copy" followed by the timestamp stored in formattedDate
var name = SpreadsheetApp.getActiveSpreadsheet().getName() + " Backup " + formattedDate;

// gets the destination folder by their ID. REPLACE xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx with your folder's ID that you can get by opening the folder in Google Drive and checking the URL in the browser's address bar
var destination = DriveApp.getFolderById("YOUR-FOLDER-ID");

// gets the current Google Sheet file
var file = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId())

// makes copy of "file" with "name" at the "destination"
file.makeCopy(name, destination);
}
1 Like

So you’re using a Spreadsheet ID in Integromat instead of choosing it from a list?

so that you can… send via a webhook?

I’ll tell you what I do.
In Integromat I create a small data store and use it store various parameters related to my apps, including the Glide App ID, backend SpreadsheetID, backup FolderID, API parameters, etc, etc.

Every Webhook from Glide includes the AppID, so in most of my scenarios the first thing I do is take that and do a lookup in my data store, and then I have all the parameters that I need for that app. It also helps me to confirm that the webhook has come from one of my apps.

1 Like

Super cool idea! :+1:

Yes, exactly. This is to prevent mismatches when changing the spreadsheet or any other backend changes.

I’m having some challenges with the Search rows module. I’ve posted the question on the Integromat community page but thought perhaps someone here can also pick it up and assist.
Thanks

I’m not in that group and have to request to join. Can you share what your problem is in screenshots?

Edit: They approved my request to join, but please post your questions here next time instead of linking it somewhere else that requires joining a group.

I’m reposting it here so others can chime in.

As you can see in the screenshot, I’m using a Data Store to fetch the SpreadsheetID but manually input the Sheet name.
I actually have two issues, which might be connected:
I’m not seeing the headers, although I selected “yes”. Screenshot attached.
When I “Run this module only” I input the SpreadsheetID and the “Equal to” parameters manually. It runs fine (no error) but doesn’t find anything (0 output bundles). When I change the module setting an input a manual “Equal to” parameter (SpreadsheetID left as a parameter which I input manually when running only that module), it works fine and it finds the correct row.

From what I know, the column names would only show up when you have a non-dynamic Spreadsheet/Sheet ID as an input.

Let’s say you choose “Spreadsheet 1” and “Sheet 1” as your inputs, then Integromat knows exactly what to look at, and show the column names for you.

If you try to make it dynamic like this one, I don’t think they return the names. Is that a big problem for you?

I’m not sure I understand your second question. Do you mean when you manually input something it works?

1 Like

Yes. It’s very strange. If I search for a dynamic parameter, it returns zero bundles. If I input a manual parameter, it returns as requested. I’ve of course checked letter-case, exact string (copy&paste), selected case-insensitive, etc. Nothing works :frowning:

I was able to solve it by using “contains (case insensitive)” instead of “equals”. Not sure what’s the story here…


Update:
So the above solution seemed dodgy (at best) from the start so I kept on digging. As it seems, we need to first “stringify” a parameter using the “Compose sting” tool. Than we filter against that composted string (=Text) and viola… works :slight_smile:

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.