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 :)