Create Multiple Columns in Google Sheets if they do not exists
With Google Sheets, I want to create multiple columns in an existing worksheet if they do not exist already.
For example: I want to create 3 columns - “A”, “B” and “C” if they do not exist. Currently, I am only able to do the creation part by having 3 “Create Spreadsheet Column” steps. My question is -- can I do this with fewer steps + how to perform the check to skip the columns that are already present?
Btw is there any other way to only create a column if it doesn't exist using the existing steps/actions?
Hey Troy, can you also suggest how to perform this in fewer steps? I can think of one way which might require 3 steps
Step1 -- `GET /v4/spreadsheets/{spreadsheetId}/values/{range}` to get the header row.
Step2 -- Python Zapier Step to check which are the missing columns
Step3 -- POST /v4/spreadsheets/{spreadsheetId}/values:batchUpdateto create the missing columns
Let me know if this makes sense. Thanks!
Also, in `GET /v4/spreadsheets/{spreadsheetId}/values/{range}` how to pass the spreadsheetid and range values? Do we also need a code step beforehand? Please let me know if there is any guide available for using API request step.
Thanks.
@MohitMayank
Also, in `GET /v4/spreadsheets/{spreadsheetId}/values/{range}` how to pass the spreadsheetid and range values? Do we also need a code step beforehand? Please let me know if there is any guide available for using API request step.
Hey @Troy Tessalone I meant how to get this data and pass it in Zapier?
Hi there, @MohitMayank
Hope you don’t mind me popping by to lend a hand. We don’t have a specific guide for how to set up an API Request for Google Sheets specifically but we do have a more generic guide on how to set up a API Request (Beta) action that you might find useful: Set up an API request action.
For the Python action, I’m not sure how familiar you are with writing code but wanted to suggest trying our AI-powered feature for Code steps which can help to generate the necessary code that would check which columns are missing and output the columns that need to be added. You can learn more about that feature here: Generate a Code step using AI (Beta)
From there you could use either another API Request (Google Sheets) action to add multiple columns using that Google Sheets API batchUpdate endpoint you found. Or use a Create Spreadsheet Column (Google Sheets) action inside a loop (See Loop your Zap actions guide for details) to create each of the missing columns.
Hope that helps to get you pointed in the right direction. If you run into any issues at all just let us know, happy to assist further!
Hey @SamB thanks for the help. Just one concern here, for API Request for Google Sheet, the URL requires details like spreadsheet ID. Can we get those details from a previous Zapier step like “New or Updated Spreadsheet Row”?
@MohitMayank
You may need to use other Zap steps with the GSheets API to get the Spreadsheet ID and/or the Worksheet ID.
@Troy Tessalone can you please suggest which step can help me get the spreadsheet id?
Hi @MohitMayank!
Can we get those details from a previous Zapier step like “New or Updated Spreadsheet Row”?
The New or Updated Spreadsheet Row trigger doesn’t output the ID for the spreadsheet. But you can see the ID listed in can be seen in the Spreadsheet dropdown menu (in grey text) just underneath the spreadsheet’s name.
For example:
You can also see the ID for the spreadsheet when viewing the spreadsheet in your browser here:
Alternatively, if you need to have the spreadsheet ID set dynamically then you could potentially use a Find a File (Google Drive) action to locate the relevant spreadsheet and obtain it’s ID.
Keen to ensure you’re all set here so please keep us in the loop on how you get on with this!