Question

Google sheet fill in rows instead of create row

  • 4 March 2021
  • 6 replies
  • 285 views

Hi everybody,

 

I’m trying to accomplish the following.

 

Step 1. Zap retrieves Data

Step 2. Zap puts data in Google Worksheet 1

Step 3: Worksheet 2 I’ve a formula saved that links back to Worksheet 1. This formula needs to work directly with the data put their in Step 2.

So if the zap puts the following data in worksheet 1

  • Cell A1: 20
  • Cell B1: 40

Than in worksheet 2:

  • Cell 1 has the formula: =Sheet1!A1+B1. Should resolve in = 60

However the problem is as following:

 

Because Zapier Creates a row for instance in row 1. My formula in worksheet two automatically gets updated and the formula jumps from = A1+B1 to = A2+B2. And there is no data, if than another row is added the same thing happens again. And on and on.

 

I already tried to write the formula as =Sheet1!$A$1+$B$1 but that didn’t work. So I need the Zap to Fiill a row instead of Create. Or another solution ofcourse :)

 

Thanks in advance


This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

6 replies

Userlevel 7
Badge +9

Hi @Info Leapforce,

This is actually a common error, most of us faced it at some point, the way I tackle this one is by using “Update Row” instead of “Add Row” 

Here is the process for that:

  • Add a new column in the sheet/workbook 1, in the column’s cells add this formula =ISBLANK(B2), populate as many cells you need, maybe around 1000. So your sheet will look like this:
  • Now in Zapier add a find row step, and in the search value field use “True”, and keep bottom-up False as we need the first “True” row.
  • Then use update row action in Zapier

Using this method, your formula in Worksheet 2 will work fine.

Hope this helps.

Hi @robschmidt ,

 

Thank you so much for your explanation. I think your solution will work, however, now I’m facing the problem that Zapier tells me that there is a problem writing to Google Sheets. While I didn’t change much.

 

Another question I’ve is. The data that needs to be filled in are more than one row. Will update spreadsheet row not update only one row?

 

Please find my zap information in these pictures.

 

 

 

Thank you so much for helping out!

Userlevel 7
Badge +9

Hi @Info Leapforce,

Firstly, about the error, it’s likely something that can be resolved by reconnecting your account, check out common problems with Google Sheet from Zapier: https://zapier.com/help/doc/common-problems-with-google-sheets-on-zapier

 

As for updating more than one row, that has to be done using Looping by Zapier, check out its documentation here: https://zapier.com/help/doc/loop-your-zap-actions

Here’s how it will work for your Zap:

  • You are getting more than 1 item from the API, right?
  • Add a new action after Second API Action, use Loop by Zapier, and add the line items received from the API
  • In the following actions, you need to map the iterated items from the Loop. 
  • You may first need to generate line-items from the API, to do that, add a Fomatter action above Looping and use a utilities’ line item function.

Hi @robschmidt ,

 

Thank you so much! I will have a look into that :)

Hi @robschmidt ,

 

The problem with writing to the Google Sheet was solved by Row changing in step 4 too: 3. Update Identifier: TRUE. To a common Row number.

 

This is of course not what we want but it did show me that there seems to be a problem with that particular step. It seems it can’t find that Row.

 

My Output is as following:

 

Userlevel 7
Badge +11

Hey @Info Leapforce!

Are you still running into that error? If so, we have a help guide that covers some of the potential causes of “There was an error writing to your Google sheet” errors that you may find useful here - Google Sheets error: “There was an error writing to your Google sheet.”  

If you work through the suggestions in that guide and still need some help then the Support Team will be able to dig into this with you. You can contact them using the Get Help form.

Hope that helps!