Coding level: Intermediate
Duration: 30 minutes
Project type: Google Workspace Add-on
Objectives
- Understand what the solution does.
- Understand what the Apps Script services do within the solution.
- Set up the environment.
- Set up the script.
- Run the script.
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.
Prerequisites
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.
The Google Apps Script API turned on in the Apps Script dashboard settings.
Set up your environment
Open your Cloud project in the Google Cloud console
If it's not open already, open the Cloud project that you intend to use for this sample:
- In the Google Cloud console, go to the Select a project page.
- Select the Google Cloud project you want to use. Or, click Create project and follow the on-screen instructions. If you create a Google Cloud project, you might need to turn on billing for the project.
Turn on the Google Apps Script API
This quickstart uses the Google Apps Script API.
Before using Google APIs, you need to turn them on in a Google Cloud project. You can turn on one or more APIs in a single Google Cloud project.In your Cloud project, turn on the Google Apps Script API.
Configure the OAuth consent screen
Google Workspace Add-ons require a consent screen configuration. Configuring your add-on's OAuth consent screen defines what Google displays to users.
- In the Google Cloud console, go to Menu > APIs & Services > OAuth consent screen.
- For User type select Internal, then click Create.
- Complete the app registration form, then click Save and Continue.
For now, you can skip adding scopes and click Save and Continue. In the future, when you create an app for use outside of your Google Workspace organization, you must change the User type to External, and then, add the authorization scopes that your app requires.
- Review your app registration summary. To make changes, click Edit. If the app registration looks OK, click Back to Dashboard.
Set up the script
Create the Apps Script project
- Click the following button to open the Share a macro
Apps Script project.
Open the project - Click Overview .
- On the overview page, click Make a copy .
Copy the Cloud project number
- In the Google Cloud console, go to Menu > IAM & Admin > Settings.
- In the Project number field, copy the value.
Set the Apps Script project's Cloud project
- In your copied Apps Script project, click Project Settings .
- Under Google Cloud Platform (GCP) Project, click Change project.
- In GCP project number, paste the Google Cloud project number.
- Click Set project.
Install a test deployment
- In your copied Apps Script project, click Editor .
- Open the
UI.gs
file and click Run. When prompted, authorize the script. - Click Deploy > Test deployments.
- Click Install > Done.
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, 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.
Run the script
Make sure the Google Apps Script API is turned on in your dashboard settings. Take the steps in the following sections to run your script.
Copy the macro
- In Sheets, 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.
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 Google Apps Script API is turned on in the dashboard settings and repeat the steps listed under Copy the macro.
Review the code
To review the Apps Script code for this solution, click View source code below:
View source code
Code.gs
UI.gs
appsscript.json
Contributors
This sample is maintained by Google with the help of Google Developer Experts.