Are you looking for a way to automate your workflow using AppScript while maintaining the Advanced Protection Program (APP) enabled?
In this guide we will explore how to run a script and create triggers while keeping the APP security standard enabled.
APP or Advanced Protection Program is a security program built into your Google account, it’s meant to protect the accounts of journalists, activists, CEOs, and security aware people from hackers.
APP is a very strong security posture that you can enable in your Google account to make it way harder to hack. It includes malware protection and enforces the use of Security Keys, which is a best practice when it comes to account security.
APP blocks untrusted scripts from running in your account to protect the data from exfiltration since these scripts have read and write access to your data.
So the question now is how can you run a script while maintaining the APP enabled in your account? to do this, we will use an example script that does some work in a Google Sheets document.
function copyValueToAnotherSheet() {
// Get the active spreadsheet
const ss = SpreadsheetApp.getActiveSpreadsheet();// Get the source and destination sheets
const sourceSheet = ss.getSheetByName('Sheet1');
const destinationSheet = ss.getSheetByName('Sheet2');
// Check if both sheets exist
if (!sourceSheet || !destinationSheet) {
SpreadsheetApp.getUi().alert('Please make sure both "Sheet1" and "Sheet2" exist.');
return;
}
// Get the value from cell A1 in the source sheet
const valueToCopy = sourceSheet.getRange('A1').getValue();
// Find the last row with content in column A of the destination sheet
const lastRow = destinationSheet.getLastRow();
// Write the value to the next empty row in column A of the destination sheet
destinationSheet.getRange(lastRow + 1, 1).setValue(valueToCopy);
}
The example script copies the value from cell A1 in Sheet1 and writes it to the next empty row in the first column of Sheet2.
When you have APP enabled, a simple code like this does not run out of the box.
After a while looking for a quick solution, I asked a college that knew a lot about Google Workspace and he pointed me out this solution.
The OnlyCurrentDoc annotation
The first solution we will try is to add the OnlyCurrentDoc notation to the beginning of the script to make it run.
The notation OnlyCurrentDoc at the header of the script sets the OAuth scope to the current document, and allows us to run the script.
This is the easiest solution to the problem, but it has some limitations. For example, what happens if you try to create a trigger so the script runs periodically?
The automation problem
If you want this script to be 100% automated, which means adding some trigger logic, for example, to run every day at 00:00 UTC. You would have to create the trigger.
Here’s an example code:
/**
@OnlyCurrentDoc
*//**
* Copies the value from cell A1 in Sheet1 to the next empty row in column 1 of Sheet2.
*/
function copyValueToAnotherSheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getSheetByName('Sheet1');
const destinationSheet = ss.getSheetByName('Sheet2');
if (!sourceSheet || !destinationSheet) {
// This check is important for when the trigger runs automatically.
console.error('Please make sure both "Sheet1" and "Sheet2" exist.');
return;
}
const valueToCopy = sourceSheet.getRange('A1').getValue();
const lastRow = destinationSheet.getLastRow();
destinationSheet.getRange(lastRow + 1, 1).setValue(valueToCopy);
}
/**
* Creates a time-driven trigger that runs the copyValueToAnotherSheet function daily.
* You only need to run this function ONCE to set up the trigger.
*/
function createDailyTrigger() {
// Check if a trigger for this function already exists to avoid duplicates.
const triggers = ScriptApp.getProjectTriggers();
let triggerExists = false;
for (const trigger of triggers) {
if (trigger.getHandlerFunction() === 'copyValueToAnotherSheet') {
triggerExists = true;
break;
}
}
if (!triggerExists) {
ScriptApp.newTrigger('copyValueToAnotherSheet')
.timeBased()
.everyDays(1) // Run every 1 day
.atHour(0) // Set the hour to 0 (midnight)
.inTimezone('UTC') // Set the timezone to UTC
.create();
console.log('Trigger created successfully.');
} else {
console.log('Trigger for copyValueToAnotherSheet already exists.');
}
}
The new function CreateDailyTrigger() will create the trigger in AppScript to run automatically every day.
But if you try to run the code, a very ugly error pops up
This time the error is different, and even more scary, the OnlyCurrentDoc notation did not fix the problem.
In order to create a trigger in AppScript having APP enabled, we have to explicitly grant the required permissions to the app to access the user data.
This is done using an OAuth Consent Screen, and here’s how you do it.
1. Create a GCP project
The first step is to Create a Google Cloud project. If you are unsure on how to create a project then refer to the official GCP documentation.
Once you have your project, you will have to find the Project Number, you can find the project number in IAM & Admin / Settings. Alternatively, you can get the project number by running this in your Cloud Shell:
gcloud projects describe $(gcloud config get-value project)\
--format="value(projectNumber)"
Keep this project number at hand since we will use it in the next step.
2. Change the project in AppScript
Go to the AppScript > Project Settings and you will notice that the GCP Project is set to Default.
With your project number, now click on Change project.
Paste your project number and click on Set project.
And that’s all! You have set your GCP project number to the AppScript project. Now it’s time to configure the OAuth application.
3. Configure the OAuth application
In GCP go to the Google Auth Platform, we will configure it for our AppScript application.
Click on Get started, and in the project configuration set all the required information. In the Audience section select External.
Once you have the OAuth platform configured, click on Google Auth Platform / Audience and click on Add Users in the user prompt add your email address.
This set your user account the Test User for the new application.
Notice the warning message:
While publishing status is set to “Testing”, only test users are able to access the app. Allowed user cap prior to app verification is 100, and is counted over the entire lifetime of the app.
This means this app will be run only by a handful of users which are the test users you allow.
Now you have to go to the Google Auth Platform / Data access section and add the scopes required by our AppScript application.
In this example, as shown in the screenshot, the required permission I need for my app is https://www.googleapis.com/auth/script.scriptapp
Your application might need a different permissions set. But for my use case that’s the permission I need. You will have to configure the exact permissions needed for your application.
Click on Add or remove scopes and manually add the scope (permissions) requested by AppScript.
Once you do that, your permission is added to the sensitive scopes section.
And that’s it. Now you have an OAuth consent screen ready to be used by the application. Let’s try it!
4. Run the script with the OAuth consent screen in place
Now you can go back to the script, and click on run, and it will prompt you with the following message:
Google hasn’t verified this app, but that’s ok because it’s your personal app, it’s not a production application. Click on Continue. Now you will be presented with the OAuth Consent Screen.
In the consent screen you have to select all the permissions, and authorize the app to use the required scopes. The OAuth screen will go back to the application, which now is able to create the trigger!
And that’s it! You managed to create an function and a trigger in AppScript having Advanced Protection Program enabled! Kudos!
In this writeup we have two solutions to run AppScript scripts while keeping Advanced Protection Program enabled.
The first one, using the OnlyCurrentDoc annotation is great when we have to run script that will be triggered upon on user actions, like onOpen or onChange. And if we want to run more complex automations we have to create an OAuth consent screen and set the script to run in a GCP project.
I hope you found this guide valuable. The journey continues in the next chapter!
Source Credit: https://medium.com/google-cloud/navigating-appscript-restrictions-in-googles-advanced-protection-program-32e201dc98c8?source=rss—-e52cf94d98af—4
