Archive Data Add-On for Google Sheets

API Connector Add-On for Google Sheets

Check out my API Connector Add-on to easily connect and pull data from thousands of platforms (e.g. Shopify, Harvest, Mailchimp, ActiveCampaign, VWO, YouTube, etc.) directly into 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.

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

archive-data-img3

Start using Archive Data now: Archive Data

PRIVACY POLICY

The Archive Data add-on runs entirely within Google Sheets, and none of your Sheets data is accessed, viewed, or stored by our servers. You may consult the complete privacy policy here.

QUESTIONS? 

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

47 thoughts on “Archive Data Add-On for Google Sheets”

  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.

    Reply
    • 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.

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

    Reply
    • 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.

      Reply
      • 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?

      • 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.

    Reply
    • 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?

      Reply
    • 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.

      Reply
    • 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.

      Reply
  4. Hi Ana, this is great! One quick question – is there any way to set up a Google Sheet so it archives once per hour, but only for a limited (but cyclical) period? More specifically, I’m trying to run a once-per-hour archive of my sheet but I only want that to occur every Monday. Is there any way to make that happen?

    Reply
    • Thanks, Kevin! Archive Data doesn’t currently provide any custom scheduling functionality, it’s just hourly / daily / weekly / monthly. I guess you could set up a bunch of weekly archives for different hours, but that would be a pretty clunky solution. I’ll consider adding something better in the future.

      Reply
  5. Is there any way to manually trigger only certain archives? I’d like to have the option to choose between several sources to archive from, but can only see an option to trigger all the archives.

    Reply
    • Sorry, you can only manually trigger all at once. This is because the main functionality of this add-on is around scheduling, so only the scheduling functionality lets you select individual archive processes.
      Out of curiosity, would you find manually triggering a single archive much more convenient than simply copy/pasting it?

      Reply
  6. Hi Ana,

    thanks for this great add-on. It really helps us to save a lot of time.

    One quick question: we would like to use your add-on to archive an unknown number of data rows for the last day and add it to another spreadsheet tab. Something like that:

    Source: Data!A2:B
    Destination: Archiv!A2:B

    While this works fine for the first time, we receive an error at the second time, because the ranges don’t have the same size:

    “The number of rows in the data does not match the number of rows in the range.”

    Is there any way to get this running?

    Thanks a lot in advance for your answer.

    Reply
    • Hi Tobias, thanks for the comment. That’s an interesting request. Unfortunately Archive Data only works with ranges of a fixed size, there is no way to handle a dynamically sized range. Sorry for the inconvenience. Perhaps you could choose a range that’s large enough to cover your maximum number of rows, and then filter out blanks with a formula on your end?

      Reply
    • Hi Tobias, you mentioned that you found a way to make dynamic range work. Can you explain how ? I’m facing the same problem.
      @Ana Thanks for a great tool !

      Reply
      • Hey Egon, sorry, there’s no support for dynamic ranges. However, depending on your setup maybe you can just make your range large enough to cover your expected limits. Any “extra” cells will just copy over as empty.

  7. Hello,
    Thank you for a great add-on! Is there a way that once data has been archived to a new sheet, that the original sheet removes the information on it?

    Thank you!

    Reply
    • Hey Marco, glad you like the add-on. Archive Data will only copy, not delete, data while archiving. Maybe you could achieve this by putting all your data onto a second sheet, and building in some IF() logic so your source range only pulls in that data before/after a certain date.

      Reply
  8. Buenas, no consigo agregar un nuevo archivo me sale en la columna de la derecha el siguiente mensaje”a página drive.google.com ha rechazado la conexión.”

    Reply
    • This is a bug from Chrome that occurs when logged into multiple Google accounts at the same time. Until Google fixes it, you can resolve this issue by logging out from all other accounts before running the add-on, or by opening in a new incognito window.

      Reply
  9. i understand that this add-on doesn’t do data removal. just wondering if there is a data removal scheduling add-on you can recommend?

    The use case is like we are using the google sheet to handle our church kids weekly attendance. The teachers mark the roll from the sheet, and the data gets populated to different part of the church for follow-up and caring work. Currently the range of each sheet the teachers uses, is the one that is used for reporting and everything else. However, we would like to move the data to the archive sheet of each class, so all the reporting and data processes are happening from the archives sheet, while the sheet for teacher can stay small and making the roll marking task easier. Otherwise, as the data grow, teachers need to scroll quite a bit to get to the right date to mark the roll.

    Thanks & regards
    K

    Reply
    • Hi Kevin, I can understand your use case as you explained it but can’t recommend any add-ons as I’ve never used that kind of removal function myself. Sorry for any inconvenience.

      Reply
    • Hey Sebastián, sorry, I’m not sure which language or textbox you mean, but there isn’t any way to edit the interface of Archive Data. If you explain a little more about what you’d like to do, I’ll be happy to consider it for a future release.

      Reply
    • Hey Szymon, you can’t select more than 1 cell at the same time, but you can set up multiple archive processes, and have each process send to a different destination cell. Would that help you accomplish what you’re looking for?

      Reply
  10. please improve this trick. I want to stick my selection on the “Named” row and when I will move up or down this selection go with this named row. please.

    Reply
  11. Hi Ana, I am a big fan of your add-on, so I have submitted to the pro version. I was wondering

    1.) why I cannot schedule more than 6 archives, all of them work fine, but I don’t get no 7 and 8 up and running..

    2.) Is there a way only to run one new archive instead of the archive all now function?

    THX
    Tom

    Reply
    • Hey Tom, thank you, I’m glad you like Archive Data! Though I’m not sure what you mean about a pro version, as far as I know there’s only a free version 😀

      For your questions,
      1) there is no limit to the number of archive processes you can run, so I’m not sure why #7 and #8 aren’t running. To troubleshoot, please first try running them manually to make sure there’s no error in the setup. If you don’t see any errors, then it sounds like you may be hitting Google Sheets’ own limits, like their 6 mins/execution limit. In that case, try reducing the number or frequency of archives, or the total amount of data being archived at one time.

      2) Sorry, you can only archive all. That feature was never really developed because the main purpose of this add-on is related to scheduling. However, if you’re only archiving one, I think it should be pretty easy to just copy and paste the data for that set.

      Please let me know if I can help clarify anything else.

      Reply
  12. Is it possible to paste multiple ranges? For example:
    From: Company!A2:G9999,K2:L9999
    To: Database!A2:I9999
    And if not which I suspect not, how could I accomplish the task I need to be completed in a row orientation.

    Reply

Leave a Comment