Automating Google Sheets: Remove external links and create dated backups

Other Questions Google Sheets
1

Hey everyone! I’m trying to figure out how to automate a process in Google Sheets. I want to do two things:

  1. Remove all external links from my spreadsheet, keeping only the values.
  2. Create automatic backups of the sheet with just the values, naming each file with the current date.

Does anyone know if there’s a way to set this up? Maybe using Google Apps Script or some other method? I’m not very experienced with coding, so any help or suggestions would be really appreciated. Thanks in advance!

2

I’ve tackled this issue before. For removing external links, you can use Google Apps Script with the getValues() and setValues() methods. This effectively copies just the values, discarding any formulas or links. As for automated backups, create a script that duplicates your sheet and uses Utilities.formatDate() to add the current date to the filename. Set up a time-based trigger to run this script daily or weekly. While it might seem daunting at first, there are plenty of resources online to help you piece together the necessary code. Start small and build up your script gradually. If you get stuck, the Google Apps Script documentation is quite helpful.

3

hey SurfingWave! i’ve done smthin similar before. for removing links, u can use the PASTE_VALUES function in Apps Script. for backups, set up a time-driven trigger to run a script that copies ur sheet and names it with date. its not too hard, even for beginners. lemme know if u need more specifics!

4

I’ve implemented a similar automation in my work. Here’s what worked for me:

For removing external links, you can use the copyTo() method in Apps Script, specifying the PasteType as PASTE_VALUES. This effectively creates a clean copy without formulas or links.

As for dated backups, I set up a time-triggered function that creates a new spreadsheet, copies the values from the original, and names it using the current date. The key is using SpreadsheetApp.create() and then the Utilities.formatDate() function for naming.

The trickiest part was getting the triggers right, but once set up, it runs smoothly. Just be mindful of your Google Drive storage limits if you’re creating frequent backups.

If you’re new to Apps Script, start by recording a macro of your manual process. This gives you a base script to work from and modify.