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
- Click the button below to open the Share a macro Apps Script project.
Open the project - At the top-left, click Overview .
- At the top-right, click Make a copy
.
- In your copied project, at the left, click Project Settings
.
- Under Google Cloud Platform (GCP) Project, click Change project.
- Enter your Google Cloud project number and click Set project.
Step 2: Install a test deployment
- At the left of the Apps Script project, click Editor .
- Go to the
UI.gs
file. - In the function dropdown, select onHomepage.
- Click Run.
- When prompted, click Review permissions and authorize the script.
- At the top of the Apps Script project, click Deploy > Test deployments.
- In the dialog, click Install > Done.
Step 3: Get the macro script and spreadsheet information
- 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.
- Click Extensions > Apps Script.
- In the Apps Script project, at the left, click
Project settings
.
- Under the script ID, click Copy.
- Set the script ID aside for use in a later step.
- Open or create a new spreadsheet where you want to add the macro. You must have permission to edit the spreadsheet.
- 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.
- On the right sidebar, open the Share Macro add-on
.
- Under Source macro, paste the script ID.
- Under Target spreadsheet, paste the spreadsheet URL.
- Click Share macro.
- Click Authorize access and authorize the add-on.
- Repeat steps 2-4.
Step 5: Open the copied macro
- If it's not open already, open the spreadsheet to which you copied the macro.
- Click Extensions > Apps Script.
- 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.