Google Apps scripts are great for controlling and automating Google Sheets, Google Docs, Google Data Studio, in fact you can use them to interact with most apps in the Google ecosystem.
If it’s been a while since you’ve created or set one up, you’ll be surprised that you can’t just create a script and go anymore. Google changed the process in April 2019.
We’ll go through how to write a script in another post, for now we’ll talk about how you go about setting one up from scratch with the new process.
The Google Script Editor
At this point you’ll need to write some code and create a script, if your script interacts by pulling data from URLs or connecting to APIs you’ll most likely encounter the below window when trying to debug or run the script.
If you click on ‘Review Permissions’ it’ll ask you for the account you want to continue with.
If you try and validate with your Google account it will fail with the below:
Authorising and Running App scripts – GCP Setup
Google changed the rules in Apr 2019. Scripts no longer automatically authorise with the Google Cloud platform (GCP), you need to manually set up a project and associate the script for them to work (more details here and here).
Go to console.cloud.google.com and create a new project you also need to add credentials (more details in the above links).
Depending what you have set up already, click the drop down next to Google Cloud Platform, it’ll open up a window:
Click on the ‘New Project’ link:
Give the project a name, note that this also becomes the project ID, while the project name can be changed at a later date, the project ID will stay fixed. I suggest using a single project for all your internal scripting needs if you’re not worried about script performance logging etc.
Click the menu, go to API & Services – Credentials:
At the credentials screen click on Create Credentials – OAuth client ID:
You’ll now need to configure the consent screen by clicking Configure consent screen:
For non G-Suite accounts can only select external:
Fill out the appropriate fields:
You’ll probably want to validate your domain for extra validation:
Once you’ve gone through creating the consent screen re-select Create Credentials – Oauth client ID as you’ll need to create OAuth 2.0 Client credentials:
Once complete click Create and you’ll be presented with the Project Number which you will need for the app script to run, not this number.
Linking the App Script with the GCP Project
After you gone through all that, you’re finally ready to link this project to the app script to allow it to run.
Go into the Google Script Editor select Resources – Cloud Platform project.
Once you’ve Set Project, you should get a confirmation success message:
The below is a condensed summary of the instructions I found on the Google Developer site, which I’ll try and locate the link for again:
1. If you don’t have a suitable GCP project, create a new one by following the Create a project instructions. Otherwise, open the GCP console Manage Resources page and locate an existing GCP project to use. You must have edit permissions for the GCP project and you must be able to view its settings in the Google Cloud Platform Console. The new project can’t be one that was automatically created by Apps Script.
2. Set a memorable GCP project name so that you can more easily locate it in the GCP console Manage Resources page. This name is also used to identify the script when Apps Script asks users to authorize it.
3. Determine the Project number of your GCP project.
4. In the Apps Script editor, open the script whose GCP project you want to replace.
5. Click Resources > Cloud Platform project.
6. In the Change Project section, paste the project number you copied into the text field, then click Set Project.
7. A warning screen explains the effects of changing the Cloud Platform project. Read the notice carefully, and click Confirm.
Re-test the App Script
Re-test the script by either debug or run:
You’ll need to click allow.
Once you’ve gone through this whole process you should be able to run your App Script.
Each time you create a new script, you can re-use the same Project Number created in Google Cloud Platform for authentication of the new scripts you create. You may want to create a different GCP project for scripts of different types if you want to segment and monitor script performance more easily.
Feel free to look me up on Linkedin if you have any questions!