Google Sheets, IFTTT and the 2000 row limit - a query [EDIT - and Solution]

Hi All,

I know there had been some chat over the past couple of years about using the ‘onchange’ function in scripts to bypass the 2000 row limit when using IFTTT and google sheets.
What I’ve been surprised by is that I can’t find a version of the script knocking about the web or these forums. I’d expect that it’s a fairly universal script across all users utilising IFTTT/sheets. I’m not a big coder, but am capable of modifying scripts as necessary. It’s starting one from scratch that had me vexed.

Does anybody have it working and willing to share the code?

Simply, all I want to do is access all my data in a single spreadsheet/text doc as required. I actually find it baffling that I can view my station history at https://tempestwx.com, but can’t just download it directly from there. I bought this to trial as a potential solution for future remote research purposes, but the lack of simple access probably makes it not fit for that purpose.

[EDIT] - The solution (thanks to Richard Creasey and https://github.com/covrig/homeassistant-trackermap?fbclid=IwAR2ysHFPuqslgiG-lPz9st2w3CQqTq8IXcp3d6x4quQCp7Zy6GoYonvhSTY for their input and the source code used).

For those interested in not have a new sheet every 2000 rows the following appears to work -

  1. Create a folder in your google Drive for the data.

  2. Create two blank Google Sheet files. Can be called anything but for simplicity here they will be called ‘Source’ and ‘Archive’. Check the sheet name in each file is Sheet1 (can be renamed but update code).

  3. You can add column headers to the ‘Archive’ file, but leave ‘Source’ file blank

  4. Point your IFTTT automation at the ‘Source’ file

  5. Open the ‘Source’ file and go to Tools\Script Editor . Replace everything with the following function that moves the data from “Source” to “Archive”, deleting the source data in the process:

function onChange(event) {

var source = SpreadsheetApp.openById(‘Source_ID_Here’);
var archive = SpreadsheetApp.openById(‘Archive_ID_Here’);

var sourceSheet = source.getSheetByName(‘Sheet1’);
var destSheet = archive.getSheetByName(‘Sheet1’);
var sourceData = sourceSheet.getRange(‘A1:T2000’).getValues();
destSheet.getRange(destSheet.getLastRow()+1,1,sourceData.length,sourceData[0].length).setValues(sourceData);
sourceSheet.getRange(‘A1:T2000’).clear({contentsOnly: true});
}

  1. Update the source and archive IDs in the script - each sheet Id can be found in the URL e.g. 1z027iLRD_HpZOSnufhmb1KeS4qr4dyK2trwpURNBgz3 ).

  2. Create a trigger for the function above. In the script editor go to Edit\Current project's trigger , give the script a name, then select create a new trigger. Ensure ‘Choose which function to run’ is set to onChange, and ‘Select event source’ is set to ‘From Spreadsheet’ and ‘Select Event type’ is set to ‘On Change’. Save and Exit.

  3. Back in the Script press the run icon to make sure it doesn’t throw up any errors. Most likely error will be a mismatch between file and/or sheet names and the script.

2 Likes

@ChrisA thanks for the Script.

With your instructions, it took 15 minutes to get it working.

It is exactly what I was looking for.

Exactly what I was looking for. Thank you so much!

My IFTTT applet keeps stopping recently. I get the error ’ Action failure message: bad status code 400 received’. I’ve tried reconnecting my WeatherFlow account to IFTTT multiple times with no success. Anyone else with this issue?

With my original version of the code, if you have been using IFTTT with Google sheets, you will an error like this. I made some changes to avoid this. Copy the code of my published spreadsheet. Before You will also need to manually delete the rows in the Google sheet which IFTTT is updating.

Thanks for your reply. I don’t know what happened to my IFTTT applet. It’s been working fine for months and all of a sudden it stopped. The error said that it couldn’t add a line to the Google sheet. I finally took some time today to see what was going on. If I manually deleted the first row of the source sheet the applet would run one time and then error again. So I changed the line in the function from:

sourceSheet.getRange('A1:W2000').clear({contentsOnly: true});

to

sourceSheet.deleteRow(1);

and it started working again. So I’m not sure why it stopped working and I’m not sure why it started working again. I don’t know what difference it makes. Thanks again for your help.

1 Like

Hi - I’m trying to get this working for my Tempest data as I’m tired of copying/pasting info to a local spreadsheet. Everything is working fine except when I get to my Source file and start creating the script. (Script Editor is now under Extensions, not Tools) When I create the script and try to save it, I get the following error message:

SyntaxError: Invalid or unexpected token (line 6, file “Code.gs”)

If I try to run the function to test it, I get a message that there are “No functions to run”. Likewise, I can’t set a trigger for the function as the file can’t be saved.

Any help is greatly appreciated!

2+ years (too) late but… I was just setting this up for myself and I was on MacOS when I did, it copied directional single quotes into place instead of generic ones. I had to delete and replace all of the single quotes (button between the semi-colon and return keys) to have proper code. And as noted above, they (Google) split out script functionality to be under Extensions. Otherwise, I got it working relatively quickly and migrating my data to a better Sheet.