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.
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.
This article walks through how to set up an archive process.
CONTENTS
- 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
- Privacy Policy
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".
Notes:
- 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
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.
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.
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.
ScriptError: Authorization is required to perform that action.
Hi, I received this when I try to archieve, what may be the possible cause?
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.
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.
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.
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?
How to set source tab and range from different spreadsheet? Using =IMPORTRANGE() formula
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.
Is there a way to get more than 19 archives?
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.
Update - you can now save (many) more than 19 archive processes.
hi can i use it to archive all the data at the sheet to another sheet or tab?
You can archive to any sheet (tab) within your workbook, but not to a separate document.
Hello! Thanks for the great tool! Is there a way to archive every 5 minutes?
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.
Bummer! Thanks for the response!
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?
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.
Hi, i think it will be great if I can set a certain condition to filter which rows/columns I'd want to archive.
Thanks for the suggestion! I'll keep it in mind for future development.
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.
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?
You can now manually trigger only certain archives instead of just all at once.
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.
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?
Hi Ana, I found a way to make it work. Once again thanks a lot for this great amazin add-on.
Awesome! Glad you got it to work and thank you for the kind words.
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 !
@Dev and @Ana: i'm facing the same problem: could you explain how to work with dynamic ranges?
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.
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!
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.
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."
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.
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
Hi Kevin, I can't recommend any add-ons as I've never used that kind of removal function myself. Sorry about that.
Hey! Is there any way to change the language or edit the textbox? Thanks!
Hey Sebastián, 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.
Hi Ana
Is it possible to have more than one cell in "Select destination sheet and range:" field?
BR
S
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?
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.
Sorry, I'm not really sure what you're asking for, can you please clarify?
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
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.
Update: you can now run just one archive instead of all at once.
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.
Hi Blake, you could make 2 separate archive processes, would that work?
Hi Ana! I was wondering if there's a way to save only the last x number of generated values? Meaning as opposed to writing over the same value or generating a long chain of values, just keeping three or four and the last value is replaced periodically by the ones behind it?. Thank you for your help,
Josh Pandolfi
Hey Josh, sorry, Archive Data only lets you paste over the same range or over subsequent ranges, there's no function to choose a variable number of ranges. You can periodically delete columns/rows from your sheet and it will still continue the archive process as before, does that help?
I'm sure there's a way to make that work....is there a way to do it in which the first value in line would remain the most recent and the next would be the second most recent, etc.? Cheers for the help!
Josh
Archive Data always puts the most recent archive at the end, but you could use a Sheets function to sort the data in reverse order in a separate sheet (haven't tried this, but here are some potential methods).
Or perhaps assigning a date and time value to each one when it it generated?
You can include the current date/time in your source range, with a formula like
=today()or=now(), then it will get stored when your archive runs. That should also help simplify the process of re-sorting data later.Hello Ana,
Is there a way to run manually a "save archive" by macro?
you are great. Don't worry.
So, lets me try explain better. I want to use google spread sheet script to run archive data.
Sorry, there's no way to hook into the add-on code if that's what you mean, it runs in its own sandbox. You can only trigger the add-on functions through the sidebar.
I added this Add-On to my google sheets, but every time I try to add archive range it says drive.google.com refuses to connect
That's a bug on Google's side related to being logged into multiple Google accounts at the same time. Log out of your other Google accounts or open it up in a new incognito window, that will solve the problem.
Hi Ana,
Great product!
Regarding the time the API triggers when is set to "hourly":
Is it possible to set an exact minute?
i.e. every hour and 10 minutes (9:10, 10:10, 11:10, etc.)?
And: from what moment does the API starts counting the hour by default? Is it at the time you save it for the first time?
Thank you.
Hi Gonzalo, thank you for the nice comment! Unfortunately, we can not set the exact minute. Google manages triggers and they pick a random time within the hour. Once they've set the time, the minute remains consistent, though. So if it runs at 2:50, it will always run on the :50.
I'm not totally sure what you mean about when the hour starts to count, can you please clarify?
Please excuse my bad english.
You already answered this question by telling me that it is Google that decides when the update is triggered and that it will always run on the same minute every hour.
Thank you very much.
A suggestion:
Re: "Paste To Next Empty Range"
It would be very good if you could choose whether to add the new data to the left (adding a new column) or to the right as it currently is.
Adding the new data to the left would show the most recent data in view when opening the spreadsheet.
Thanks again.
Thanks, that's a good suggestion. I'll consider adding that.
Hi Ana, is this something that you're able to implement? I would find it extremely useful too. Thanks
Oh my, I still like this idea but I haven't updated this extension in a long time because I'm too busy with API Connector. I can't promise anything but will try to add this option at some point.
I am trying to see if this would help me script it where once a cell that is formatted to eventually reach 100%, once it hits 100% transfer the data in that row to another tab (Archive) and remove it from the current tab. Or does this only transfer and not remove?
Correct, this only copies/pastes data, it doesn't delete the source data.
Ana - I'm trying to archive data daily and I have the plug-in and it works, but I have to create the sheet as the destination where to archive. I don't want to over-write the data, I want to archive data in a new sheet every day and change the sheet name to todays date? Can I do this some how with the archive plug-in?
Hey Chris, sorry, this add-on isn't that advanced :p It can only copy to the next empty range in a single sheet, it doesn't have logic to print into a new sheet. Some other people have asked for that too so I'll try to add that at some point, but unfortunately the current version probably isn't what you're looking for.
Ana, we continue to love your add-on, finding many uses for it in our schools.
I have been trying to use it to get a user's email address and details of the body of those emails by label. I have had totally hit-and-miss results through using Google scripts, so I have been trying to set up the process through Google's api.
I have also (shamefacedly) been unable to do this.
Would you be able to provide some direction?
Thanks very much,
Brian
I'm glad to hear you find it useful! Sure, please send me a message and I'll be happy to help you out.
Hi I am having an issue opening the addon. It is greyed out keeping me from opening it. Can you please help?
Hi there, I think this is the same issue mentioned here. To address, please log out of your Google accounts except the one you're using with Sheets, or open a new incognito window.
I have used the app and it was working fine and then about two weeks ago it slowed down the archiving to the point where even one or two cell archives take at least a minute. I tried to delete and rewrite each but it is still much slower than before. I deleted all but two very simple archives and it is still much slower than before.I have not added any extra function in the sheet. I have logged out and back on google. Any ideas on what might be slowing it down.
All Archive Data does is copy/paste from one location to another, so can you please check what happens when you copy and paste without using the extension? It sounds like there may be excess recalculation in the sheet, where changing a cell causes a cascade of functions recalculating. Please also test setting up your archive in a fresh new sheet with a limited amount of data to see if the problem persists (if not, then you'll know it's related to something about the original data sheet).
I did the new sheet test this morning. It is much faster so it must be something embedded in my sheet. Thank you for your prompt response.
Hey Ana,
The person who originally set up the Archive function for my Google Sheet has left the company, and the email address they used will soon be deleted. Is there a way to ensure that these functions will continue to run after the account is deactivated? Or will someone with an active email be required to recreate the function?
You can avoid triggers de-activating by just making sure there's another user in the sheet. As long as they a) have Edit access and b) open the extension at least once, the triggers will automatically switch to their account. Otherwise the trigger will stop running when the account is deactivated. Please let me know if I can clarify anything further.
This makes sense - I appreciate the quick response. Thank you!!
My sheet stopped auto-saving each day, have been running smoothly for 3 months.
1) It won't save data on the scheduled setting
2) When I run manually it saves the data
Any pointers on how to get it up and running again?
That's strange, I haven't changed anything on this side so I wonder if you're hitting Google's scheduling limits.
Their most relevant limits are 6 mins processing time per hour, 90 mins per day, and 300 total sheets running schedules. Could you be approaching these limits by any chance? Or could you test a schedule in a fresh sheet to see if it's some issue with the current sheet?
Hi There, Is it able to archive the data every 2 weeks. Or maybe just allow for every (X) days?
Or even better, Maybe allow for archiving of data a formula like a =ARCHIVE(START) function?
This won't exactly work out to every 2 weeks, but you could set up 2 different monthly triggers at different times, would that work?
Overall, I appreciate your suggestion to add in more granular time controls. I haven't been working on Archive Data much since I've been busy with my main extension, but I will try to add scheduling options this year.
Hi Ana,
I regularly use your excelent extension Archive Data.
Today it started to give me this error "Service Error: Spreadsheets"
I did not change any configuration. Yesterday was working ok. I tried it in different computers with the same result.
Can you help me?
Thank you.
Hey Gonzalo, I haven’t seen this error before, but based on this thread you may be able to resolve it by adding blank rows to the bottom of your report. Can you please check if that works?
Hi Ana, I added 1000 rows and tried again but the problem persists.
Gotcha, thanks for checking. I haven’t made any recent updates to this extension, so let’s see if anyone else reports this issue, maybe it’s a glitch on Google’s side.
Hi again Ana, still giving this error “Service Error: Spreadsheets”
I also tested in different browsers with no success.
Hey Gonzalo, sorry it's still not working. Changing browsers or computers won't help as the issue is on Google's side, but I can't say exactly what the issue is. It might be related to your sheet, can you please test running archives in a different sheet?
Hi again Ana, I tried in a blank sheet and it works OK.
I will continue running test to find an explanation to this error and will let you know if I find it 😉
Hi again,
I discovered that it gives the error when there is an embedded image in any cell of the range when executing the extension.
But this did not happen before. If there was an image in the cell, the text 'CellImage' appeared.
Maybe this explanation can help you solve the problem.
Thank you.
Interesting! Thank you for that helpful troubleshooting, we'll look into that.
Hi. Where do I access the archived sheet(s)?
Thanks-
They archive into the same spreadsheet where you saved the archive process. So you should be able to just open your sheet to see them (check the destination tab + cells).
Is there a way to get a timestamp of when each archive is pulled? I have my account set up every hour but I would like to know the day and time of each report.
Sure, just add
=now()to your source data set, then the current value of that formula will be copied over when the archive process takes place.Hi! When I use this function and delete information from the original spreadsheet it deletes also ends up deleting it from the archive page. How do I stop this? As my aim for using this was to be able to delete the Info as it goes into the archive page from the original spreadsheet to stop the original source being so cluttered
By default data will be pasted into the next empty range, so it shouldn't overwrite or delete any existing data. Can you make sure you've checked the "Paste to Next Empty Range" option?
Hi Ana, I have my sheet set to archive every monday between 5-6. It isnt working, but if I do it manually it runs with no issue.
That's odd, I'm not sure what the issue could be. Is it a very large amount of data? If so it could be hitting Google's processing limits for background runs. Or do you have multiple archive processes that could be overwriting one another?
Any plans to enable fornightly archiving (different to twice a month archiving)?
Fortnightly would mean once every 2 weeks, correct? I can't really see adding that as a preset option because there's not much demand for that cadence, but at some point next year I'd like to add more customizable scheduling which should allow for this and other options.
Yes, every 2 weeks. I have a 2-weekly roster than i would like to archive before the roster auto-updates
I see, thank you for the clarification. I'm afraid only weekly will work for now, so you'd need to clean it up by deleting the extra weeks.
not working pls how can i solve this https://prnt.sc/3mMYmGV7Gglq
Looks like a temporary glitch on Google's side. Please refresh your sheet and the full menu should appear.