Copy Google Spreadsheet with Google Apps Script

A Comprehensive Guide to duplicate Google Spreadsheet with Google Apps Script.Using Google Apps Script, we can easily copy a sheet to another spreadsheet. In this guide, we're going to focus on how to duplicate a spreadsheet using Google Apps Script, also known as a Google Script. It's quite a straightforward process and requires only basic knowledge of Google Apps Script.

To use the Google Apps Script copy sheet script, you first need to replace "spreadSheetID" with the ID of the Google sheet you want to copy, and "newSheetName" with the desired name for the new sheet. After this, you run the copySheet function. This function will create a copy of the source spreadsheet, effectively duplicating the spreadsheet, and then return the ID of the new spreadsheet for future use.

Here's how the Google Apps Script to copy a spreadsheet looks like:

function copySheet() {
  // Replace "spreadSheetID" with the ID of the Google Spreadsheet you want to copy
  // you can find ID in the URL after the letter 'd/' in docs.google.com/spreadsheets/d/

  var spreadSheetID = "1rKCcPx0TIBv7VFY_ufBf7fm6gg1g2EnjxoynNWQ2sLQ";

  // Replace "newSheetName" with the desired name for the new Google Sheet
  var newSheetName = "insert-new-sheet-name-here";

  // Use Google Apps Script to get the source spreadsheet by its ID
  var sourceSpreadsheet = SpreadsheetApp.openById(spreadSheetID);

  // Google Apps Script is used to make a copy of the source spreadsheet
  var newSpreadsheet = DriveApp.getFileById(spreadSheetID).makeCopy();

  // The Google Apps Script then renames the new spreadsheet to the desired name
  newSpreadsheet.setName(newSheetName);

  // It also opens the new spreadsheet
  var newSpreadsheetId = newSpreadsheet.getId();
  var newSpreadsheet = SpreadsheetApp.openById(newSpreadsheetId);

  // Optional: save the ID of the new spreadsheet in a variable for future use
  var newSheetID = newSpreadsheet.getId();

  // Optional: return the ID of the new spreadsheet
  return newSheetID;
}

To duplicate a Google Spreadsheet using Google Apps Script, we've used here the DriveApp and SpreadsheetApp classes. These two classes are integral when you want to create an Apps Script that can duplicate a sheet or copy a sheet to another spreadsheet. Essentially, this Google Script is a convenient tool for duplicating spreadsheets, copying sheets to a new spreadsheet, or even copying a sheet to another sheet.

Here, I wrote a detailed explanation of the usage of makeCopy in Google Apps Script.

Use cases examples

The Duplicate Google Spreadsheet with Google Apps Script can be used in a variety of ways:

Regular Backup: With this script, you could schedule regular backups of your Google Spreadsheets. This is especially useful if multiple people have editing access to the spreadsheet and you want to ensure you have a saved version at specific intervals.

Template Spreadsheets: If you have a master template spreadsheet that you use as a starting point for multiple projects, you could use this script to create a new copy for each project. This way, you can maintain the integrity of the original template spreadsheet.

Data Migration: If you have a lot of data stored in a Google Spreadsheet and need to move it to a new one without manually copying and pasting, this script will do the work for you. This is particularly useful when restructuring your data storage or moving information to a new account.

Testing Changes: If you want to test new scripts, calculations, or formats on a Google Spreadsheet but don't want to risk the current version, you can use this script to create a copy and make your changes there.

Archiving Data: If you have a Google Spreadsheet that is updated daily or weekly, you can use the script to create a record of the spreadsheet at those points in time. This will allow you to look back at the data as it was at that point in time.

Sharing Data Without Giving Edit Access: If you need to share a spreadsheet's data with someone but don't want them to have the ability to edit the original sheet, you can create a copy to share with them.

Remember that this Google Apps Script to duplicate spreadsheets can be set up to run automatically or be triggered manually, depending on your specific needs. These examples showcase its versatility and potential for time-saving in various workflows.

How to setup automatic trigger for copy spreadsheet once per week

In order to set up an automatic trigger to copy a spreadsheet once per week using Google Apps Script, follow these steps:

  1. Open the script editor in the Google Sheets by clicking on Extensions > Apps Script.

  2. Paste your copySheet function into the Apps Script editor if it's not there already.

  3. Click on the clock-like icon, which stands for "Triggers" on the left sidebar. This is where you will create the trigger.

  4. Click on the "+ Add Trigger" button in the bottom right of the Triggers page.

  5. In the "Choose which function to run" dropdown, select your function (copySheet).

  6. For "Select event source", choose "Time-driven".

  7. For "Select type of time based trigger", choose "Week timer".

  8. In "Select day of week", choose the day of the week you'd like your script to run.

  9. In "Select time of day", choose the time of day you'd like your script to run.

  10. Under "Failure notification settings", decide how often you want to be notified if your script fails and select the appropriate option.

  11. Click "Save".

Now, your script will automatically run once per week at the day and time you've chosen. Remember that the specified time is approximate. The script could run up to 15 minutes later than the specified time.

Learn Google Apps Scripts basic functions.

Published