Copying a Tab in Apps Script

This post is a corrected post after AI explains the code included in the book “Google Apps Script 101: Building Work Automation For Free”. Added a strikethrough when editing what AI has written, and added color when edited by author

Table of Contents

1. Summary
2. Code
3. Code Explanation
4. Example
5. AI Prompt

Summary

In this blog post, we will learn how to copy a tab in Google Sheets using Apps Script. We will explore the code that copies a specific sheet to the same spreadsheet and to a new spreadsheet. Finally, we will retrieve the URL of the newly created spreadsheet.

Code

Code Explanation

The copyTab function begins by getting the active spreadsheet using SpreadsheetApp.getActiveSpreadsheet(). It then retrieves the sheet named ‘Sheet2’ using SS.getSheetByName('Sheet2').

A new spreadsheet is created using SpreadsheetApp.create('newSheet'). The copyTo method is used to copy ‘Sheet2’ to both the original spreadsheet and the newly created spreadsheet.

When you select a range and use copyTo, you must have set the destination to the range. However, if you select a tab (sheet) and use copyTo, set the Google Spreadsheet file as the destination.

Finally, the URL of the newly created spreadsheet is logged using NEW_SS.getUrl().

Example

Let’s say you have a Google Sheets document with two sheets: ‘Sheet1’ and ‘Sheet2’. You want to create a new spreadsheet and copy ‘Sheet2’ to both the original and new spreadsheets. The code mentioned above can be used to achieve this.

After running the copyTab function, you will have two copies of ‘Sheet2’ – one in the original spreadsheet and another in the newly created spreadsheet. The URL of the newly created spreadsheet will be logged in the Apps Script Logger.

AI Prompt

Write a function that copies a specific sheet to the same spreadsheet and to a new spreadsheet. Log the URL of the newly created spreadsheet.