Google Spreadsheet <> CloudRun Jobs — Trigger & Monitor

In this article I’m going to explain how you can trigger & monitor Google Cloud Run job directly from Google Spreadsheet.

Example of UI interface inside spreadsheet that updates every 1 min

Cloud Run Jobs:
https://cloud.google.com/run

Cloud Run jobs are a way to run long-running tasks on Google Cloud Platform. They are stateless, event-driven, and serverless, which means that you don’t need to worry about managing servers or infrastructure.

Generally, Cloud Run provides a serverless platform that gives developers a way to deploy containers without configuring/tuning infrastructure (machines, node pools, network etc) — we find it very efficient to deploy stateless & standalone jobs mostly because it’s simple and cost effective.

Apps Script
https://developers.google.com/apps-script

Apps Script is a cloud-based platform that lets you write JavaScript code to automate tasks and extend Google Workspace products. It’s easy to use and doesn’t require any prior programming experience.

Generally, Apps scripts are the way of adding superpowers to drive products by applying some javascript code.

On the spreadsheet you want click Extensions -> Apps Script it will open an editor and also create new project in Apps Script that will be attached to the spreadsheet. Every function that we will define can be bound to images or events inside the related spreadsheet.

Next, on the App Scripts page go to Settings page and enable Show “appsscript.json” manifest file in editor — this will allow us to define extra scopes needed while the end-user will authenticate with Google.

Next, go to the editor part and replace the Code.gs file content with the following cloudrun_trigger.gs:

Replace the following parameters:

const cloudrun_jobs = ['gcp-cloudrun-job-name'] // list of cloud run jobs name
const cloudrun_project_id = "gcp-project-number" // project number in GCP
const cloudrun_project_name = "gcp-project-name" // project name in GCP
const cloudrun_region = "gcp-cloudrun-region" // cloudrun jobs region

const job_start_row = 2; // the row number to start the status display.
const job_link_column = "J" // column that will contain a link to the executed job.
const job_status_column = "K" // column that will the status of the J[n] job.
const update_timestamp_column = "L" + job_start_row //the column that will contain the last update timestamp

Next, go to appsscript.json and append all missing tags as describe in appsscript.json — this will import dependencies and add the needed scopes to identify vs Google Cloud Platform.

After you finish those configuration — you can select TriggerCloudRun function in the Apps Script editor section and debug the function to see that the job in Google Cloud Console runs successfully.

Final thing, to make this solution work each user that you want to give ability to execute and monitor the job — must get Cloud Run Invoker role in the relevant Google Cloud project — a simple solution in to create a google group and add the group to Google Cloud IAM with the Cloud Run Invoker Role -this way you can control the access from Google Groups and not form GCP IAM.

In conclusion, this technic can allow you as a developer/product — ability to trigger remote jobs and check there progress directly from Google Spreadsheet — even if you're not going to use it as described in this article — I hope this ability will help you along the way.

Thanks for reading,

Ben Mizrahi
Cloud & Data Architect