Tempest Google Sheet Archive and 24h Dashboard using IFTTT WeatherFlow Applet

An IFTTT WeatherFlow Applet can be used to send station updates to a Google Sheet every 60 seconds ( IFTTT Applet stores 1min data to Google Sheets). The following three Google Sheets can be used to create a full archive the all the data received from the stations and to create a 24-hour dashboard.

Observation log

Observation log Archive

  • The Sheet1 sheet receives the updates from IFTTT

  • When there are many thousands of rows of data, Observation log Archive is very slow to open.

Observation log Dashboard

  • The Sheet1 sheet receives the updates from IFTTT.

  • Sheet 1 has a script that runs on the OnUpdate event of Sheet1. This script deletes the oldest rows to ensure only 1440 (60*24) rows and copies the rows to ‘24H Data’ sheet

  • The purposed of the ‘24H Data’ sheet ensures that rows referenced by the ‘Cleaned Data’ and the ‘Dashboard’ sheet are consistent

  • The ‘Cleaned Data’ sheet references data in the ‘24H Data’ sheet and adds a column to covert the TiimestampEpoch date to Google Sheets date

  • The ‘Dashboard’ sheet weather data statistics and graphs for the last 24 hours.

Installation

  1. Copy the three Google to your IFTTT/Weatherflow folder on Google Drive

  2. Set the permissions for the scripts in Observation log and Observation log Archive to allow it access

  3. Clear (not delete) the rows in the ‘24H Data’ sheet to get rid of my data

  4. Go to your IFTTT webpage and create the applet with WeatherFlow service to update Observation log Google Sheet

7 Likes

nice usefull write up!

I like it very useful for TS and a lot other stuff. Thanks for share it.

Hi folks,

To copy the Google Sheets, you first need to log into your Google account, then open one of the three Google Sheets you intend to copy, and then select the File/Make a Copy menu item to copy to your Google Drive.

The is no need to request access to the Goggle Sheets. You already have all the access needed to make the copies you need.

Great write-up. I added a link to your second post at the bottom of the original one to make sure someone sees it if they didn’t read the rest of the thread.

I am new at this and not really sure what I am doing :slight_smile:

I did make copies of the obs log and obs archive but not sure how those get populated.

I did successfully create the “smart weather station” data sheet and it is populating with my data.

What populates the obs log and obs archive sheets? Do they have to have a specific naming format?

Thanks… George

IFTTT populates the two sheets.

Perhaps I missed this step…

Set the permissions for the scripts in Observation log and Observation log Archive to allow it access

Where do I do that?

Thanks… George

@george1 After copying the sheets, open your copy of Observation log and click Tools/Script Editor. This will open a new tab called “Archive Observation Log”. Then click on the Run button. You will be asked to give permission to the script.

I am getting warning messages but after I get past them giving permission… I get the following in the log.

9:12:44 AM

Notice

Execution started

9:12:46 AM

Error

Exception: You do not have permission to access the requested document.

onChange

@ Code.gs:11

I think you do have not made a copy of Observation log .

To make a copy and then give the script permission:

  1. Observation log
  2. Click on the menu item: File/Make a Copy
  3. A new Google Sheet will be created called: Copy of Observation log. In this Google Sheet click Tools/Script Editor. This will open a new tab called “Archive Observation Log”.
  4. Then click on the Run button. You will be asked to give permission to the script.
  5. Choose your account. You will get the worrying message "Google hasn’t verified this app. Click on “Advanced” and the Go to “Archive Observation Log (unsafe)”.

Rememer you have to copy Observation log Archive and Observation log Dashboard Google Sheets.

I am still not getting it to work. This week I will start over and do a screen video going through the steps as I understand them.

If OK… I will then post on youtube for your review so you can see what I am doing wrong. Is that OK?

George

I think when we try to copy the observation.log file it opens and runs the script before we can make the copy. Since when I go into tools/Script Editor it says this:

Sorry, unable to open the file at this time.

Please check the address and try again.

@cswilly I get an access denied message when I try to open the Observation Log Archive sheet. Maybe the permissions got changed on your end?

You need to copy the spreadsheet to your Google Drive.

Thanks for the reply. But that’s not the issue. I’m not able to open that specific sheet (Observation Log Archive). It gives me the access denied message when I click on it to try to open it. No problem opening and copying the other two sheets.

1 Like

The instructions did not tell you that inside the script, you will need to change the file ID associated with the file
source → observation log
archive —> observation log archive
dashboard —> observation log dashboard

The script inside the observation log that you copy from cswilly has the file ID uniquely to his Google Drive. This file ID is different for the file you copy to your Google drive. They’re the text in your URL after https://docs.google.com/spreadsheets/d/.

Make sure after you make the changes to these file IDs, save the script and then execute it. Google will ask you permission the first time. Choose your account. You will get the worrying message "Google hasn’t verified this app. Click on “Advanced” and the Go to “Archive Observation Log (unsafe)”. Repeat this with the script inside “Observation Log Dashboard” file in your Google Drive.

Create “observation log archive” by making a copy from your “observation log”. No need to do anything with script here.

I got mine to work only for a few minutes now so hopefully I will see the reading move from observation log to observation log archive and observation log dashboard.

hope this helps…

Cheers mate