The free Google Sheets add-on Archive Data helps store snapshots of historical data in your Google Sheets reports and dashboards. It allows you to set a schedule to paste from row or column X to Y, automatically creating a record of your original data.
You have a monthly dashboard that uses the "Google Analytics spreadsheet add-on" or 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 March's data in a new column before it gets overwritten by April's data.
This article walks through how to set up an archive process.
- Before You Begin
- Step 1: Add New Archive
- Step 3: Set Source and Destination Ranges
- Step 3: Choose Archive Options
- Step 4: Name and Save
- Appendix: Run Manually
BEFORE YOU BEGIN
Click here to install the Archive Data add-on from the Google Marketplace.
STEP 1: ADD NEW ARCHIVE
After installing the plugin in Google Sheets, click Add-ons > Archive Data > Add New Archive.
Alternatively, you can click Manage and then Add New.
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 pulling 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. Schedules will be based on the timezone of the sheet (you can view your sheet timezone by clicking File > Spreadsheet settings).
STEP 4: NAME AND SAVE
You can have multiple archives active within a single sheet, so give them clear names to differentiate. All archive processes can be viewed on the Manage screen.
APPENDIX: RUN MANUALLY
While the primary purpose of this add-on is scheduling snapshots at set intervals, there may be times when it's useful to manually trigger an archive process (maybe for testing). There are two methods to manually update archives:
- Click Add-ons > Archive Data > Archive All Now. This will immediately run every saved archive process in your sheet.
- Run individual archive processes via the dropdown menu on the Manage screen