Archive Data Add-On for Google Sheets

The free 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, automatically creating a record of your original data.

Example:
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.

archive-data-img5

 

This article walks through the process of setting up an archive using the Google Sheets add-on Archive Data.

NEW! API CONNECTOR ADD-ON FOR GOOGLE SHEETS

Check out my new API Connector Add-on to import data from thousands of platforms (e.g. Shopify, Harvest, Mailchimp, ActiveCampaign, VWO, YouTube, etc.) directly into Google Sheets.

STEP 1: ADD NEW ARCHIVE

After installing the plugin in Google Sheets, click Add-ons > Archive Data > Add New Archive

archive-data-img1

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-img2

Notes:

  1. 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).
  2. 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-data-img4

  1. 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.
  2. 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.
  3. Keep formatting. Choose whether you want your archive to retain the original formatting from the source range, or paste as plain text.
  4. 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.

archive-data-img3

There is currently a limit of 19 archive processes per sheet.

Start using Archive Data now: Archive Data

QUESTIONS? 

Just leave a comment or send a message using the Archive Data Contact Form.

P.S. If you like this add-on, please support me with a review or rating (link). This is a totally free tool and your support keeps me going! Thank you.

 

Comments:19

  1. Hi, I am using your great Google Sheets add-on! One question, is there a way I can check exactly what time the archive runs? The scheduling options only give a 1-hour range.

    1. I suggest adding the following formula to an empty cell in your source range: =now()
      That formula will produce a full timestamp like ‘7/9/2018 15:30:59’. When the archive process runs, it will paste-as-values all the data in your range, including the timestamp that was current at that moment.

  2. ScriptError: Authorization is required to perform that action.
    Hi, I received this when I try to archieve, what may be the possible cause?

    1. Can you please try logging out of all Google accounts in your browser and then logging back into just the one using the add-on, or using an incognito/private window? Google sometimes gets confused about permissions when you’re logged into multiple accounts. Let me know if that helps.

      1. thank you, it works.
        I also want to know: I have multiple sheets, where each sheet may contain different data, but I only want to archive active sheet only, is this possible?

      2. I’m not sure I totally understand your question, but if you need to run archives based on some condition, would it be possible for you to make a new sheet with formulas that pull in your ‘active’ data? Then you could set up on archive for just that sheet.

  3. Is there an option to set up the archive to run daily? On the drop down menu for frequency I can’t see the options (just a blank box ) When I move my curse it selects words I can’t see, but when I select them I only see Weekly and Monthly as options.

    1. Yes, you can set the archive to run hourly, daily, weekly, or monthly. I’m not sure why the options are showing up as a blank box for you but I suspect it’s an issue with your browser. Would you be able to open the add-on in another browser (e.g. IE or Firefox) to check if the issue persists?

    1. You can’t archive directly from another spreadsheet, but you can first use =IMPORTRANGE() to pull data into your spreadsheet, and then select and set that imported data as your source range.

    1. Hi Tiffany, good question! The limit is due to Google’s document properties storage limit, which gets filled up after 19 values. It should be possible to implement string compression or some other method to increase the limit, but I wasn’t aware of any demand for more than 19 archives. May I ask how many (approx) you could see yourself using in a single doc? That will help me figure out the best solution.

    1. It will use the timezone of your sheet. You can click File > Spreadsheet settings if you need to check what that is.

    1. Thanks, Ron! Google does not allow add-ons to run triggers more than once an hour. If that ever changes I’ll add more frequent triggers right away.

Leave a Reply

Your email address will not be published.