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 -
-
Create a folder in your google Drive for the data.
-
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).
-
You can add column headers to the ‘Archive’ file, but leave ‘Source’ file blank
-
Point your IFTTT automation at the ‘Source’ file
-
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});
}
-
Update the source and archive IDs in the script - each sheet Id can be found in the URL e.g.
1z027iLRD_HpZOSnufhmb1KeS4qr4dyK2trwpURNBgz3). -
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 toonChange, and ‘Select event source’ is set to ‘From Spreadsheet’ and ‘Select Event type’ is set to ‘On Change’. Save and Exit. -
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.
