Note: if your scheduled archives are not running, please try clicking Add-Ons > Archive Data > Manage, and re-save your archive details. This usually resolves the problem, but please send a message if you’re still having issues.
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 row or 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.
Using your mouse or keyboard, highlight the range you wish to save regularly, and click ‘Set’. That range will now appear in the box under “Select source tab and range”. Next, select where that range should be pasted, and click ‘Set’ under “Select destination tab and range”.
- Archive Data works by automatically saving data in a range from one location to another at a specified time. This range can be vertical (columns) or horizontal (rows).
- Your ranges can include multiple columns or rows, it’s not limited to just one. However the source and destination must be exactly the same size.
STEP 3: CHOOSE ARCHIVE OPTIONS
There are 4 options:
- Archive to Columns or Rows. You can select whether you want your range to be saved to the next available empty range of columns or the next available empty range of rows. The starting point is the range selected in the Destination Range field.
- Paste to Next Empty Range. By default this is checked. It means that the archive will automatically populate the next empty range to the right (for columns) or below (for rows). Leave it unchecked if you prefer to overwrite the data in the same place each time the archive process runs.
- Keep formatting. Choose whether you want your archive to retain the original formatting from the source range, or paste as plain text.
- Schedule Report. 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.
P.S. If you’re interested in other Google Sheets add-ons, you can also check this post on Top Google Sheets Add-Ons for Spreadsheets (+User Counts).