How to Automate Google Sheets with Zapier

With any database, you need to be able to automatically create, read, update, and delete rows in your tables. In this tutorial, you'll learn how to automate basic Google Sheets operations with Zapier.

How to Automate Google Sheets with Zapier

With any database, you need to be able to automatically create, read, update, and delete rows in your tables.

In this tutorial, you'll learn how to automate basic Google Sheets operations with Zapier. We'll utilize a Google Form to make requests to Google Sheets in order to automate the following:

  1. Create new rows
  2. Find existing rows
  3. Update existing rows
  4. Delete existing rows

Finally, you'll learn how to take output data from any of these actions and send it to other apps; i.e Gmail, Slack, etc.

Setting Up Your Zap

Start off by creating a Google form and add the following fields to the form; 'full name', 'phone number', 'email', and 'how did you find out about us?' For the purposes of this example, we call this form, 'Lead Form.' You can make all the question field types short-text except for 'how did you find out about us?' which will be a multiple-choice question. Go ahead and make a test submission.

Then, create a new Google spreadsheet called 'Lead Database' and add the field names from the Google form as columns in your spreadsheet.

Create a new zap in Zapier, and add Google Forms as your trigger app. For the event, select 'New Form Response.' This is an instant trigger and your zap will trigger when a new form response is received.

You'll then need to select the specific Google form that will trigger the event. When you click on the input field, you'll see a list of form names associated with your account.

PRO TIP: If you're having a hard time finding the correct form you need, you can always look at the form ID below the form name. You can take this ID and match it up with the ID encoded into the Google form URL.


Proceed to test the trigger event, and you should see the test response you previously submitted from your Google form. Once you get a response, you're ready to add an action and start mapping fields.

Creating New Rows in Your Worksheet

Add an action and select the Google Sheets app. For the event, select 'Create New Spreadsheet Row.' This will create a new row in a specific spreadsheet.

Go ahead and select your spreadsheet.

Then select the worksheet you desire to create new rows in.

You should then see the columns of your spreadsheet represented as input fields in this action step. Proceed to map the 'full name', 'phone number', and 'email' output data from the Google form trigger step with their respective fields in the Google Sheets step.

Proceed to test the action and a new row should be created in Google Sheets. In the test response, Zapier will show you the data that was created in each column of your worksheet.

Check the worksheet in Google Sheets to see if the action step populated data correctly. As you can see below, our action step worked perfectly and populated columns A through D.

Finding Existing Rows in Your Worksheet

To find an existing row in your worksheet, add a new Google Sheets action and select the event 'Lookup Spreadsheet Row.' This event finds a row by a column and value. It will return the entire row if one is found. It also gives you the ability to create a spreadsheet row if none is found. This action is super powerful because it allows you to grab an entire row via a unique identifier. That unique identifier can be an alphanumeric ID, email address, or phone number.

After selecting your spreadsheet and worksheet, select a lookup column. You should see all your columns populate the dropdown. This is the search constraint that specifies where Zapier should look when searching for cells that match the look-up value. For this example, we made 'email' our lookup column.

Next, add the look-up value. Since 'email' is the lookup column, we map the email output value from the Google form to the lookup value input field.

If finding a row requires two columns to match, you can always add a supporting lookup column and supporting lookup value. I've never used this in practice, but it could be useful in certain scenarios.

Proceed to test the action and now Zapier will now see if any cells, within the 'email' column, have the value 'hello@joshnocode.com.' As you can see below, Zapier found a matching cell in COL$C and returned the entirety of row two.

Update Existing Rows in Your Worksheet

The ability to look up spreadsheet rows is a prerequisite to actions that update and delete rows in your worksheet. To update an existing row in your worksheet, add a new Google Sheets action and select the event 'Update Spreadsheet Row.' This will update a row in the desired spreadsheet.

After selecting your spreadsheet and worksheet, you need to specify which row Google Sheets needs to update in the 'row' field. Since, in most cases, this is a dynamic value, you'll head over to 'custom' and select the ID of the row from the previous search step. Then you can choose the column(s) you want to update for the identified row and map fields accordingly.

In this example, I just updated the 'Full Name' field to a static value 'Steve Martin' and as you can see below COL$A of row 2 was successfully updated.

Deleting Existing Rows in Your Worksheet

The process to delete rows in your worksheet is actually very similar to the process to update them. To delete an existing row in your worksheet, add a new Google Sheets action and select the event 'Delete Spreadsheet Row.' This action will delete the content of a row in a specific spreadsheet. When you delete a row using this action, it will appear blank in the spreadsheet.

After selecting your spreadsheet and worksheet, you need to specify which row Google Sheets needs to delete in the 'row' field. And again, in most cases, this is a dynamic value, so you'll head over to 'custom' and select the ID of the row from the original search step. Proceed to test and the output data should indicate that the entire row was deleted. As you can see below, the entirety of row two was deleted from columns A to Z.

Sending Row Data to Other Apps

Once you finish adding the Google Sheets actions, publish the zap and give it a few tests to make sure that you get the desired outcome you're looking for.

Then, if you need to, you can always add an action to push data from your spreadsheet to other sources like slack, Gmail, or anywhere you want.

Let's say that you want to send output data from the 'Create New Spreadsheet Row' step to a colleague on slack. Just add a Slack action step and select 'Send Direct Message' as the event.  

Then you can map output data from the 'Create New Spreadsheet Row' and populate the 'Message Text' field accordingly.

As you can see below, a message was sent to my slack username from a Zapier bot containing the output data from step two; 'Create New Spreadsheet Row.'

And there you have it. That covers the basics and there's so much more you can automate. We'll cover more advanced Google Sheets Zapier automations in future tutorials.

Please let me know if you have any questions about this tutorial. If you found this helpful, please consider subscribing to my newsletter.  


What are you trying to automate and why? I'd love to know!

Do you have an idea for a tutorial?  Share it in the comments!

Need help with an automation? Hit me up - hello@joshnocode.com

I write weekly about business process automation hacks. If that's something you're into, join me, and let's automate together.