4/23/2018 UPDATE: Recently there were a few issues reported involving the archive date scheduler. We’ve just released an updated version of the add-on, so if you were affected, please delete and re-add your archive. That should resolve any lingering issues, but if you’re still having problems after that, please get in touch.
The Google Sheets add-on Archive Data helps store historical data from your Google Sheets reports and dashboards before it gets refreshed. It allows you to set a schedule to paste from column X to Y, backing up data before it gets refreshed.
You have a monthly dashboard that uses the “Google Analytics spreadsheet add-on” and a Salesforce connector to automatically pull in new website and customer data each month. When the report refreshes and gets populated with new data, the old data is wiped out. With this extension, automatically save January’s data in a new column before it gets overwritten by February’s data.
This article walks through the process of setting up an archive using the Google Sheets add-on Archive Data.
STEP 1: ADD NEW ARCHIVE
After installing the plugin in Google Sheets, click Add-ons > Archive Data > Add New Archive
STEP 2: SET SOURCE AND DESTINATION RANGES
A window will open on the right side of your browser.
Select the range you wish to save regularly, and click ‘Set Active’. That range will appear in the box next to “Source Range”. Repeat for Destination Range.
Note that Archive Data works by automatically saving data in a columnar range from one location to another at a specified time. In other words your data must be in a vertical format; it doesn’t currently support saving data from row to row (though row to row archives will be added soon!)
STEP 3: CHOOSE ARCHIVE OPTIONS
There are 3 options:
- Paste to next empty column. By default this is checked. It means that the archive will automatically populate the next empty column to the right. Leave it unchecked if you prefer to overwrite the data in the same column.
- Keep formatting. Choose whether you want your archive to retain the original formatting or paste as plain text.
- Schedule. Choose whether the archive should run monthly, weekly, or hourly. If you’re using the Google Analytics Spreadsheet Add-on to pull in data at some regular interval, you can set this add-on to run within an hour or two afterwards to save the data after it refreshes.
STEP 4: NAME AND SAVE
You can have multiple archives active within a single sheet, so give them clear names to differentiate.
Just leave a comment or send a message using the Contact Form.