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 > Google Auth platform > Branding.
- If you have already configured the Google Auth platform, you can configure the following OAuth Consent Screen settings in Branding, Audience, and Data Access. If you see a message that says Google Auth platform not configured yet, click Get Started:
- Under App Information, in App name, enter a name for the app.
- In User support email, choose a support email address where users can contact you if they have questions about their consent.
- Click Next.
- Under Audience, select Internal.
- Click Next.
- Under Contact Information, enter an Email address where you can be notified about any changes to your project.
- Click Next.
- Under Finish, review the Google API Services User Data Policy and if you agree, select I agree to the Google API Services: User Data Policy.
- Click Continue.
- Click Create.
- For now, you can skip adding scopes. In the future, when you create an app for use outside of your Google Workspace organization, you must change the User type to External. Then add the authorization scopes that your app requires. To learn more, see the full Configure OAuth consent guide.
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.