Is it possible to updates the daily weather in an existing row in Google Sheets and move down a column each day?

Hi @zachattach 

Good question.

To give us more context, please post a screenshot with how your GSheet is configured.

Sure thing, see the screenshot above. The date can be formatted differently if needed as well. Thanks!

@zachattach 

Couple options:

1. Use these Zap action steps:
   1. GSheet - Lookup Row
   2. GSheet - Update Row
2. Why not have the Zap set the GSheet cell values for each of the columns each day when it runs. (e.g. Date, Ticket Sales, Total Sales)
   1. Essentially start with a GSheet worksheet with only the header
3. Use Airtable instead of GSheets

The Zap is skipping blank rows

The Zap may seem to skip blank rows if they have formulas in them. If you use the Create Spreadsheet Row action, rows will be added to the first blank line that the Zap sees. If a row has a formula in it, the Zap doesn't consider it as blank and will skip it. If the formula is referencing data from within the same row you can build the formula directly into the Zap.

You can use any formulas available in Google Sheets in your Zap, if the variables in the formula can be mapped from other Zap steps.

This solution won't work if not all variables in the formula are available in the Zap, as it wouldn't be possible to reference specific cells dynamically. For example, let's say you have a sheet like the one in the image below. The Zap will populate the Number and Other Number columns, but column C will be manually updated later.

In that case, you can't reference C2 in the Zap, as this would mean that every time the Zap runs, it would make a reference to C2, instead of using the appropriate row number. In this case, you should create a new worksheet on the same spreadsheet and use the =IMPORTRANGE formula. This way you'll have a sheet connected to the Zap and another sheet that imports data from the original sheet, and allows you to use formulas, sort and filter the data, among other things, without having errors on the Zap.

I think I would like to use the first option - as we already have data in the other rows (so a new row won’t work) and we can not switch to airtable for this since the team is already using sheets. However, I’m having trouble understanding how to have the zap recognize today’s date and pull in the weather data. How would i use the “Lookout value” to indicate whatever the date may be? Will this zap run automatically and update each day with the next row down?

Thanks!

@zachattach

Finding and Updating Rows in Google Sheets

You may need to add a Zap step: Formatter > Date & Time / Format Date

Formatter help articles: https://zapier.com/apps/formatter/help

All help articles for using GSheets in Zaps: https://zapier.com/apps/google-sheets/help

When in doubt, test it out.

I’m not sure formatting is the issue. It was more a problem of adding new weather data to a cell that changes each day. I’ve settled for creating a new row in another worksheet, then pulling that data into the existing worksheet. Hopefully this works as it should only pull the weather data once per day thus creating a new row once per day. Thanks for the info!

@zachattach

• Action: GSheet - Lookup Row
  • Search by today’s date
• Action: GSheet - Update Row
  • Map the Row ID from the previous step that found the row for today’s date