Best answer

Updating bottom row only before adding new row weekly

  • 25 March 2021
  • 2 replies
  • 192 views

  • Anonymous
  • 0 replies

Hello!

I´m having difficulties achieving the automation I want.

This is the scenario: I have a google sheets document. One of the sheets are updated hourly with Pipeline API. The data I´m importing is always just a total amount accumulated over time. What I want is to divide this amount in weekly amount accumulated. So I want to know what the total was in each week. I don´t want to know how much the total increased each week, but how much the total was accumulated in each particular week. So say it´s 4 this week and next week it gains 2, then I want the row for next week to show 6 and this week to still show 4. 

Now I have my sheets document set up like this:

Week Number                  Amount

=isoweeknum(today)        “Link to the cell in the sheet that is automatically imported”

 

So what I want to do with Zapier is: Every monday at 2 am replace the formula in the bottom row with the actual data.

 

And then at 3 am add new row with the same formulas.

 

The adding new row bit is quite straightforward in zapier, so I have managed that.

 

The same with updating the row. But the problem is that the row that needs updating will be shifted down by 1 each week. I have tried solving this by using =counta formula in google sheets, to get the total amount of rows that contain data, and the using this cell as a refrence for zapier for which row to update. But as far as I can see the refrence doesn´t change after I´ve set it up, even though it changes in the sheets document. 

 

I hope someone can help me with this :)

icon

Best answer by Paolo 27 May 2021, 03:02

View original

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

2 replies

Userlevel 7
Badge +8

Hi there @Lala_TopDown - Thanks for writing to the community! Oh, this sounds like you’re so close! I want to get you the help that you need, would you mind sending this over to Support so we can ensure this isn’t an issue on our side? http://zpr.io/t4NNK - Let me know if you need any further help!

Userlevel 3
Badge +5

Hi @Lala_TopDown - Happy to give some additional advice here! With regards to Google Sheets integration on Zapier, it doesn’t support formulas yet and may produce mixed results in the Zap. You can learn more here: https://zapier.com/help/doc/common-problems-with-google-sheets-on-zapier#the-zap-is-skipping-blank-rows

 Given the case, I’m thinking you may be able to address this by using one of the methods below:

  1. Using a Storage incrementer with a Google Sheet Lookup Spreadsheet Row step to update the row: https://zapier.com/help/create/storage-and-digests/storage-examples-in-zaps#zap-1-increment
  2. Using a Formatter Spreadsheet Style Formula to do the computation: https://zapier.com/help/create/format/use-spreadsheet-style-formula-functions-in-zaps
  3. Using a more robust database app like Airtable, which does support formulas: https://zapier.com/apps/airtable/integrations#triggers-and-actions

Alternatively, you may want to hire a Zapier expert to help with this particular workflow: https://zapier.com/experts

I hope that helps and let me know if you have further questions!