Copy macros to other spreadsheets

Coding level: Intermediate
Duration: 30 minutes
Project type: Google Workspace Add-on

About this solution

Manually copying Google Sheets macros from one spreadsheet to another can be time consuming and error-prone. This Google Workspace Add-on automatically copies a script project and attaches it to a user-specified spreadsheet. Though this solution focuses on Sheets macros, you can use it to copy and share any container-bound script.

How it works

The script copies the Apps Script project that's bound to the original spreadsheet and creates a duplicate Apps Script project bound to the user-specified spreadsheet.

Apps Script services

This solution uses the following services:

• URL Fetch Service: Connects to the Apps Script API to copy the source project and create a copy.
• Script Service: Authorizes the Apps Script API to avoid a second authorization prompt.
• Spreadsheet Service: Opens the target spreadsheet to add the copied Apps Script project.
• Card Service: Creates the user interface of the add-on.

Before you begin

To use this sample, you need the following prerequisites:

• A Google Account (Google Workspace accounts might require administrator approval)
• A web browser with access to the internet
• A Google Cloud project with the Apps Script API enabled
  • To create a project and enable the Apps Script API, refer to Create a project and Enable Google Workspace APIs.
• A Google Cloud project with the OAuth consent screen configured
  • To configure the OAuth consent screen, refer to Configure the OAuth consent screen.
• The Google Apps Script API turned on in the Apps Script dashboard settings

Try it

Step 1: Set up the Apps Script project

1. Click the button below to open the Share a macro Apps Script project.
   Open the project
2. At the top-left, click Overview .
3. At the top-right, click Make a copy .
4. In your copied project, at the left, click Project Settings .
5. Under Google Cloud Platform (GCP) Project, click Change project.
6. Enter your Google Cloud project number and click Set project.

Step 2: Install a test deployment

1. At the left of the Apps Script project, click Editor .
2. Go to the UI.gs file.
3. In the function dropdown, select onHomepage.
4. Click Run.
5. When prompted, click Review permissions and authorize the script.
6. At the top of the Apps Script project, click Deploy > Test deployments.
7. In the dialog, click Install > Done.

Step 3: Get the macro script and spreadsheet information

1. Open a Sheets spreadsheet that has a macro and that you have permission to edit. To use a sample spreadsheet, make a copy of the Sample macro spreadsheet.
2. Click Extensions > Apps Script.
3. In the Apps Script project, at the left, click Project settings .
4. Under the script ID, click Copy.
5. Set the script ID aside for use in a later step.
6. Open or create a new spreadsheet where you want to add the macro. You must have permission to edit the spreadsheet.
7. Copy the spreadsheet URL and set it aside for use in a later step.

Step 4: Copy the macro

Make sure the Apps Script API is turned on in your dashboard settings.

1. On the right sidebar, open the Share Macro add-on .
2. Under Source macro, paste the script ID.
3. Under Target spreadsheet, paste the spreadsheet URL.
4. Click Share macro.
5. Click Authorize access and authorize the add-on.
6. Repeat steps 2-4.

Step 5: Open the copied macro

1. If it's not open already, open the spreadsheet to which you copied the macro.
2. Click Extensions > Apps Script.
3. If you don't see the copied Apps Script project, make sure the Apps Script API is turned on in the dashboard settings and repeat the steps listed under "Step 4: Copy the macro."

Contributors

This sample is maintained by Google with the help of Google Developer Experts.