Arlo Alerts to Spreadsheet Log (Gmail to Drive)

This Recipe requires the following: 1.) Email notifications must be enabled for the cameras you wish to include in the log 2.) Gmail label(s)/filter(s) must be set up for each "type" of activity you wish to log. For example: - If you don't care about which camera was activated, only a generic "Arlo Alert" filter needs to be created in Gmail by adding a label to any email coming from "alerts@arlo.com". This label name would be put in the 'Label' field of the recipe and would take care of all Arlo Alerts that come in. - If you wish to log which camera was activated, you will need to set up separate recipes for each camera, and in Gmail you will need to set up separate filters/labels for each camera alert as well. Since the body of the Arlo Alert emails include the camera name, you will be able to set up the Gmail label filter by filtering any email from "alerts@arlo.com" and also containing the camera name. Each label would then be given a specific name such as "Front Yard Camera", "Driveway Camera", etc., with these label names being used in each Recipe's 'Label' field. ---> If using multiple recipes to generate the log, make sure to use the same spreadsheet name in the 'Spreadsheet name' field, and the same path in the 'Drive folder path' field. 3.) Since Gmail's {{ReceivedAt}} ingredient gives the Date/Time stamp in a non-standard format (i.e. is of the form "[Date] at [Time]", instead of "[dd/mm/yyyy hh:mm:ss]"), you will have to set up some spreadsheet formulas in a second sheet in the spreadsheet to parse the log data into a standard date format if you want to be able to do spreadsheet operations such as sorting, filtering, or plotting. The steps for this are: - Create a spreadsheet (using the same name to be used in the Recipe, in the same folder path) - Enter your desired headers into the first three columns of the first row of Sheet1. Leave the rest of Sheet1 blank (to be populated by the recipe) - Create a new second sheet - Enter the formulas as follows, into row 2 (starting at column 1) of Sheet2 (leaving row 1 for headers). "|||" denotes a new column. =Sheet1!A2 ||| =if(A2="","",G2+I2) ||| =Sheet1!C2 ||| =Sheet1!B2 ||| =if(A2="","",len(D2)) ||| =if(A2="","",find(" at ",D2)-1) ||| =if(A2="","",left(D2,F2)) ||| =if(A2="","",find(" at ",D2)+4) ||| =if(A2="","",right(D2,E2-H2+1)) NOTE #1: If you get an #ERROR in any of the cells, just go into the formula bar of the cell and re-type in any of the formula that includes quotation marks (both the quotation marks themselves and anything inside of them). For some reason, Drive doesn't like it when you copy-paste a formula that has a string value in it. NOTE #2: You will have a proper date/time value in column B, however it will be an Excel date value. To convert this to a different format (such as [dd/mm/yyyy hh:mm:ss]), change the data format of the column within the Drive spreadsheet. - Once the first row is entered, drag down all the formulas to row 2000 (since IFTTT creates a new spreadsheet once the spreadsheet reaches 2000 entries). If a new spreadsheet is created, you can simply follow the same steps as above and just copy-paste the spreadsheet cells directly from one spreadsheet to the other.

by mayyc

Install 140
works with
  • Google Sheets

Applet version ID 381210