How to Create Automated Snapshots of your Google Sheet
HTML-код
- Опубликовано: 5 окт 2024
- Welcome to Community Support - where you can get help with hurdles you're facing while bootstrapping your company or trying to find new ways to increase your efficiency and up your game at work.
In this video, we'll show you how to automate snapshots of your Google Sheets and have those logs stored in a separate Google Drive folder so that you can keep your logs nicely organized for future reference. To do this, we'll be using Google Apps Script to create a script that references the SpreadsheetApp library and the DriveApp library.
🎉Are you interested in a Bootstrapping Tools Community? Let me know! forms.gle/4tzv... 🎉
Get the code here:
Revised Source Code + Auto-setup of the daily scheduled job trigger: products.boots...
Revised Source Code + Auto-setup AND auto-email notifications: products.boots...
Check out the rest of our Community Support videos. You might find a video that addresses a hurdle that you're facing today! - • Community Support
🔔 Subscribe for more tips just like this: www.youtube.co...
======= KEEP LEARNING WITH Bootstrapping Tools =======
🎬 MOST RECENT VIDEOS: / bootstrappingtools
🤖 Automate with Google Apps Script: bit.ly/Bootstr...
💻 Build-Through Videos: bit.ly/Bootstr...
🚑 Community Support Videos: bit.ly/Bootstr...
🚀 Check out some of our written content: bootstrapping....
======================================================
👥 Looking for something specific? feedback@bootstrapping.tools
======================================================
💝 Support our channel 💝
Buy us a coffee: ko-fi.com/boot...
Or by tipping: koji.to/k/6wBv
======================================================
#Bootstrapped #GoogleSheets #GoogleAppsScript
🎉Are you interested in a Bootstrapping Tools Community? Let me know! forms.gle/4tzvffHiRqS7pVZY6 🎉
Get the code here:
- Revised Source Code + Auto-setup of the daily scheduled job trigger: products.bootstrapping.tools/products/google-sheets-automated-snapshots
- Revised Source Code + Auto-setup AND auto-email notifications: products.bootstrapping.tools/products/google-sheets-automated-snapshots-b
OMG this is pure gold my friend. Amazing tutorial
Glad you liked it!
Awesome video! Curious if we could retrieve the snapshot using a date and overwrite it on your main sheet?
Huh... what an interesting thought...
Technically yes... you could totally do that. You'd... have to find the file which you could do with Driveapp.getFilesByName(name) -- assuming you're using a standard template for the file names that includes the date, you could make your search query the "Tab Name + Date". You might get more than one result, but you'll just have to narrow it down.
Once you have the right file you want to overwrite the data with (basically a roll back), it's pretty easy to take in the data. Since you're probably doing this as a rollback, I would suggest using the sheet.copyTo() method so that you're not actually "overwriting data" and have a chance to review it before actually replace things in the sheet manually. If you REALLY want to just replace it all, you can do a .clearContent() on the old sheet/range and then do setValues() with the data from the snapshot ( which you get with getRange().getValues() )
Hi! This is great! The spreadsheet that I want to take snapshots of has multiple tabs--how would I need to alter the code so that the spreadsheet "log" that is generated contains all the tabs of the original? Bonus points if this can be done dynamically--so it automatically captures any new tabs that are created.
Thanks, this is great, im trying to move my workplace away from horrid sheets and towards a custom database, to eliminate potiental key errors etc,
in the meantime this, once i get it working will save me literally thousands of future hours of work I would have manually done.
defiently a WorksmarterNOTharder tag.
LOVE IT!
If you get any pushback from the migration off of google sheets (people sure love their spreadsheets), you can always use apps script to write directly into a database so that you can keep the data safe. For preventing key errors, you can do data validation or protect specific ranges in the sheet so no one can mess with it. There's lots of ways to scale up the security/efficiency of a Google Sheet while maintaining the ease-of-usability for your users.
Hi, I am interested in automating generating a snapshot in image format. How can I do that?
Hi! Thanks for this video, it’s super helpful. Quick question - if I wanted to have the data captured in the same spreadsheet, but a different tab, could I do this? Also any indication as to how?
Thanks
Hi Cristina!
To route the data into a different tab, you would need to reference that tab/sheet as a separate variable. Something like this:
const sheetOne = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
const sheetTwo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2')
Once you have the two variables, you can reference them separating to pull/push data, or do any other manipulations you need to that specific sheet.
I keep getting this error :
Exception: The parameters (DriveApp.File) don't match the method signature for DriveApp.File.moveTo.
Hi Mohamad,
What does you code look like? For the line that moves the file to the folder it should look something like this:
DriveApp.getFileById(log_file.getId()).moveTo(destination_folder)
Hi great video. Is it possible to save log name as today's date -1 and sheet1 name as the same today's date -1?
Yes you can! You'll have to calculate yesterday's date first though -- something like this:
yesterday = ( date => new Date(date.setDate(date.getDate()-1)) )(new Date);
How can I actually send the screenshot from google Sheet to my email every day?
I have several questions:
1. How do you apply the log_file.getSheetByName to multiple tabs of data. It was mentioned it’ll create file with Sheet 1 by default, but how can you work around this? For example I tried adding ‘Sheet2’ and was met with an error.
2. I’m trying to retain the format and am having trouble copying to the log file… I would also like to copy to all the tabs within it.
3. How do you automate this to take a capture at X time during the day?
Is there a way to add dynamic metadata to the snapshot files like tags, labels?
Great question, Attila!
You can use the DeveloperMetadata class to store/manage any metadata you want with the google sheet. Here's Google's documentation around it: developers.google.com/apps-script/reference/spreadsheet/developer-metadata
It's similar to ScriptProperties in the way that you have to write some code to set them first and then you can reference/update them later on. You can apply the metadata to a spreadsheet, a specific sheet, or even a range/cell.
I am interested in automatic generating a snapshot in image format , And send it to my email, How can I do that?
great vid man.. any chance you could share that code? Idk how to write code
Hi Albert!
Yeah sure, here's a github gist of it: gist.github.com/aguycalledjoe/063c2fa5c2bee72cb4f0de2ace5caa34
Just make sure to follow along in the video so you know how to edit it to your own use case.
Hi! Thank you for this, such a great help!! I have a question tho, how do we retain the format of the sheet captured? Thank you and have a nice day~
Hi velvet_seul, thanks for reaching out!
You can use "copyFormatToRange()" to retain the format of the sheet captured. To use it, you would need to take the range of the origin data and call this function to it. Then you would pass the destination range through as parameters.
For example:
originRange.copyFormatToRange(destinationSheet, columnStart, columnEnd, rowStart, rowEnd)
@@BootstrappingTools thank you so much for your prompt help and for giving me a detailed reply!! I will definitely do that. Thank you and have a great weekend!
@@BootstrappingTools Awesome tutorial video
Is there a way you can add this into your script that's saved in the github gist that you shared?
I tried adding it in my scriptapp but am not sure of the syntax and its giving me an error.
Just for the same example if the line using "CopyFormatToRange()" is added that would truly help me understand its usage better. Thanks a ton!
Thanks for reaching out!
I looked a bit more deeply into this and decided to rewrite the code to make it better (and so that copying the format over is much easier). Here's a gist of it: gist.github.com/aguycalledjoe/9d9ec6ed354b0edfa01e36dbee799db7
I also created some other versions of it if you're interested:
- Auto-setup of the daily scheduled job trigger: products.bootstrapping.tools/products/google-sheets-automated-snapshots
- Auto-setup AND auto-email notifications: products.bootstrapping.tools/products/google-sheets-automated-snapshots-b
Can the snapshot keep the same conditional formatting?
Hi Anthony!
Yeah, you apply conditional formatting to the snapshot by using the ConditionalFormatRule and ConditionalFormatRuleBuilder libraries.
You'll basically want to get the current rules from your original spreadsheet and then build them into the destination/target spreadsheet with the snapshot data.
Here's some links to the documentation around it:
developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule
developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule-builder
So I basically copied the createSnapshot function and made a createCFSnapshot function. I set var new_backup_formats = createCFSnapshot. I added this line: backup_file.getSheetByName(‘Sheet1’).setConditionalFormatRules(new_backup_formats); just before moving the snapshot into the destination folder. The script runs successfully but no formatting is applied to the snapshot. Can you tell me what I’ve done wrong?
@@anthonysaunders9011 I'll have to read the code to see what's up. Send me the code w/o any of your sensitive info (e.g. IDs) and I'll take a look.
I copied this but no new spreadhsheet file ends up in my drive.
Okay, I figured out what I was doing wrong. I wasn't running the script under the createlog menu function
Do You know if there is a way to save the selected Range in imáge format?
did you figure it out?
@@Zcockroac no hay.
Can I create an image of this data in jpg format?
Hi Faizan, that's not really possible using the standard apps script libraries. I'm curious though... why would you want an image of your data instead of the data itself?
@@BootstrappingTools i email the image of my dashboard to my boss daily. So need to automate this task
@@faizaanazam did you manage to find a solution?
bro share the code plz