Bridging the Gap: Seamless Integration for Local Google Apps Script Development

Abstract

This article explores the evolution of Google Apps Script (GAS) development, focusing on the powerful capabilities unlocked by the gas-fakes CLI. Discover how to seamlessly integrate your local development environment with GAS, enabling you to use local files, execute scripts without uploading, and build a more efficient, modern workflow.

Introduction

Google Apps Script is a versatile platform that continues to evolve. A significant leap forward in its development is the ability to work with GAS projects locally, a paradigm shift driven by Bruce McPherson's gas-fakes. This tool, and the ecosystem it has inspired, are changing how developers approach GAS development.

The gas-fakes CLI provides a sandboxed environment for local development and testing. This foundation has led to the creation of several innovative tools, including:

A sandbox for Google Apps Script: A secure environment to test code. Ref, Ref, Ref
An MCP server for safely executing Google Apps Script: For secure script execution. Ref, Ref
A Gemini CLI Extension: Integrating gas-fakes with other tools for an AI-assisted workflow. Ref
A GAS development kit: Combining gas-fakes CLI, clasp, and workspace-developer for a comprehensive development toolkit. Ref

Recently, the article "Next-Level Google Apps Script Development" demonstrated how to create tools for the MCP server using Google Apps Script in a local environment, without needing to upload the script to the cloud. Ref This highlights the increasing compatibility between Google Apps Script and local development environments. This article will further explore and demonstrate this powerful new compatibility.

Prerequisites and Installation

Before you can start building, you need to install and configure the necessary command-line tool.

1. Install gas-fakes CLI

First, install gas-fakes, the command-line tool for executing Google Apps Script locally.

npm install -g @mcpher/gas-fakes

For authorization, refer to the official Getting Started guide.

2. Verify the Installation

Confirm that the gas-fakes CLI is installed correctly by checking its help message:

gas-fakes --help

Next, test your authorization by running a simple script to retrieve the name of your Google Drive's root folder:

gas-fakes -s "const rootFolder = DriveApp.getRootFolder(); const rootFolderName = rootFolder.getName(); console.log(rootFolderName);"

If the command successfully prints the folder name, your installation and authentication are working correctly.

Compatibility in Action: Examples

Here are some practical examples of the advanced compatibility between Google Apps Script and your local environment.

1. Using a Local Value in Google Apps Script

This example demonstrates how to use a local value in a Google Apps Script.

First, create a sample text file:

echo -e "sample text1\nsample text2" > sample.txt

Next, run the following command in the same directory:

gas-fakes -a "{\"key\":\"$(cat sample.txt)\"}" -s 'console.log(args);'

The output will be:

$ gas-fakes -a "{\"key\":\"$(cat sample.txt)\"}" -s 'console.log(args);'
...using env file in /workspace/.env
...using gasfakes settings file in /workspace/gasfakes.json
[Worker] ...importing Drive API
[Worker] ...importing Sheets API
[Worker] ...importing Slides API
[Worker] ...using /workspace/gasfakes.json
[Worker] ...didnt find /workspace/appsscript.json ... skipping
[Worker] ...didnt find /workspace/.clasp.json ... skipping
[Worker] ...cache will be in /tmp/gas-fakes/cache
[Worker] ...properties will be in /tmp/gas-fakes/properties
[Worker] ...initializing auth and discovering project ID
[Worker] ...discovered and set projectId to for-testing
{ key: 'sample text1\nsample text2' }
...terminating worker thread

This shows that the text data from the local file sample.txt was used in console.log. In this case, console.log is executed as a Google Apps Script through the gas-fakes CLI.

To further illustrate this, let's put the text data into a cell on a Google Sheet.

gas-fakes -a "{\"key\":\"$(cat sample.txt)\"}" -s 'SpreadsheetApp.create("sample").getSheets()[0].getRange("A1").setValue(args.key)'

The JSON string provided with the -a argument is available as the args object in the Google Apps Script. When you run this command, a new Google Spreadsheet will be created in your root folder. Opening the spreadsheet will reveal the text data in cell "A1" of the first sheet.

These examples clearly show that local data can be directly used in Google Apps Script.

2. Two-Way Data Flow: Using a Local Value and Getting a Return Value

This example demonstrates how to use a local value in a Google Apps Script and receive a value in return.

The simple sample is as follows.

gas-fakes -a '{"key":"sample"}' -s 'const res = "The provided argument is " + args.key; return JSON.stringify({output: res});' | grep '^{"output":' | jq -r '.output'

When this command is run, the following result is returned. You can see that the provided argument is used in the Google Apps Script to return the value.

The provided argument is sample

Another sample command is as follows. Here, we will again use the sample.txt file.

gas-fakes -a "{\"key\":\"$(cat sample.txt)\"}" -s 'const ss = SpreadsheetApp.create("sample"); ss.getSheets()[0].getRange("A1").setValue(args.key); SpreadsheetApp.flush(); const bytes = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/export?exportFormat=pdf&id=" + ss.getId(), { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getContent(); return JSON.stringify({ output: Utilities.base64Encode(bytes) });' | grep '^{"output":' | jq -r '.output' | base64 -d > sample.pdf

When this command is executed, the following steps occur:

The text data is retrieved from the local sample.txt file.
Google Apps Script is executed with gas-fakes CLI, with the text data as an argument.
A new Google Spreadsheet is created.
The text data is inserted into cell "A1" of the first sheet.
The Google Spreadsheet is saved.
The Google Spreadsheet is converted to a PDF, and the binary data is retrieved.
The binary data is encoded to base64.
The base64 data is returned.
The base64 data is decoded back to binary.
The binary data is saved as a new file named sample.pdf.

You will find a newly created sample.pdf file in the same directory. This file is the exported PDF of the Google Spreadsheet. The response value can be obtained from the property output of return JSON.stringify({ output: Utilities.base64Encode(bytes) });. In this sample, an object {"output":"value"} is used. But, of cource, you can freely use the output format. But, in the current stage, the response value is required to be string data. Please be careful about this.

To use the sandbox feature (-x or --sandbox), the command is as follows:

gas-fakes -x -a "{\"key\":\"$(cat sample.txt)\"}" -s 'const ss = SpreadsheetApp.create("sample"); ss.getSheets()[0].getRange("A1").setValue(args.key); SpreadsheetApp.flush(); const bytes = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/export?exportFormat=pdf&id=" + ss.getId(), { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getContent(); ScriptApp.__behavior.trash(); return JSON.stringify({ output: Utilities.base64Encode(bytes) });' | grep '^{"output":' | jq -r '.output' | base64 -d > sample.pdf

With this modification, the spreadsheet created in the sandbox is automatically deleted when the script finishes. Note that ScriptApp.__behavior.trash(); must be placed before the return statement.

For more complex scripts, you can use a script file. The following script achieves the same result as the command above.

Save this script as sample_script.js:

function sample({ key }) {
  const ss = SpreadsheetApp.create("sample");
  const sheet = ss.getSheets()[0];
  sheet.getRange("A1").setValue(key);
  SpreadsheetApp.flush();
  const url =
    "https://docs.google.com/spreadsheets/export?exportFormat=pdf&id=" +
    ss.getId();
  const bytes = UrlFetchApp.fetch(url, {
    headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
  }).getContent();
  ScriptApp.__behavior.trash();
  return JSON.stringify({ output: Utilities.base64Encode(bytes) });
}

return sample(args);

To execute this script, run the following command:

gas-fakes -x -a "{\"key\":\"$(cat sample.txt)\"}" -f sample_script.js | grep '^{"output":' | jq -r '.output' | base64 -d > sample.pdf

This will produce the same result.

This example demonstrates the seamless integration between local data and Google Apps Script, showcasing the advanced compatibility that these tools provide.

Summary

Local Development: gas-fakes enables a local development workflow for Google Apps Script.
Seamless Integration: It is possible to use local data within Google Apps Script and receive returned values.
Command-Line Power: gas-fakes provides a powerful command-line interface for executing scripts.
Sandboxed Testing: The sandbox feature allows for safe, isolated testing of your scripts.
Ecosystem of Tools: gas-fakes is part of a growing ecosystem of tools that enhance the Google Apps Script development experience.

tanaikech/submit.md