iTranslated by AI
Ideas for Team Development in Google Apps Script
Hi, I'm luth.
I entered programming through GAS and have studied Vue/React and TypeScript as a non-programmer, but I have been developing internal tools for my team for about four years.
In a world where generative AI makes it easy to output code, non-programmers can now easily use GAS tools. However, when using them for work, we want to ensure reliability and continuity.
Therefore, I have summarized the following points based on my experience in personal development:
- Tips for GAS development in non-programmer teams
- Points unique to team development that are often overlooked in personal development
- Ensuring maintainability and readability
Many of those who have experience in team development with JavaScript or TypeScript might already be familiar with these contents, but since GAS has a relatively large number of beginners, people without development experience, and individual developers, I hope you can make use of this...!
*Ideas for team-use tools are summarized here:
Target Audience
- Developers of GAS tools shared within a company or team
- GAS beginners to advanced users
- Environments where development is done in the GAS browser editor
= Environments where a Node environment is not or cannot be installed (where Clasp cannot be used)
Points for Team Development
Points for improving the development experience. I will focus on points to note specifically for GAS.
Establish rough coding conventions
Coding conventions are rules for "how we should write code as a team."
In particular, the GAS editor has a built-in formatter (Windows: Alt + Shift + F), but its formatting rules cannot be changed, and it feels quite loose compared to Node.js's Prettier.
Here are some reference sites:
Separating development and production environments
When developing with GAS, it is common to skip Git management and rely solely on a production environment due to the small scale of development and the difficulty of setting up a management environment...
However, since even GAS-scale projects are used for business, you should prepare a development environment for "new/additional development" and "bug fixes," even if it incurs some management overhead.
Specifically:
- For container-bound scripts, copy the entire linked file, such as the spreadsheet or form.
- For standalone scripts, copy the GAS file itself.
- For related files/settings like Bots or DBs, separate them into development and production versions.
By doing this, you can expect the following benefits:
- Avoid affecting production behavior during development and testing.
- Multiple people can develop different features and integrate them into the production environment (a branch-like concept).
- If an irreversible mistake is made in the development environment, you can attempt restoration by copying the production environment (a backup-like concept).
Check project settings first
It is a good idea to check settings such as the runtime (JavaScript version) and time zone (which city's time to use during execution, which affects the behavior of Date) at the very beginning.
Runtime: V8
Time zone: (GMT+09:00) Japan Standard Time - Tokyo (Asia/Tokyo)
Change history is a bit tricky
While many Google Workspace services offer extensive change histories, GAS lacked this feature for a long time after the transition to the new editor, despite having it in the legacy version.
The change history feature was reintroduced in September 2023, but it has a few quirks...
- You can only view comparisons between deployed versions.
- The comparison shows the diff (differences between versions).
Since GAS typically doesn't require deployment unless you're developing it as a Web app, library, or add-on, this diff functionality might not feel very useful...
If you are in an environment where GitHub can be used, please try this Chrome extension:
Google Apps Script GitHub Assistant
If you can't use GitHub or clasp, I propose the following method:
A way to retrieve the contents of another GAS file using GAS and manage it via Google Sheets or Drive.
Retrieving GAS projects using GAS
Write documentation comments (jsDoc) as much as possible
When developing as a team, not everyone will necessarily read all the code. Since you likely expect functions and global constants to be reused, it is helpful to describe things like the following:
- The intention of the definition
- The types and content of parameters and return values
- Conditions under which errors occur
If you describe the data types, they will be utilized for autocompletion in the editor, which also helps prevent bugs!
/**
* Sheet name of the master sheet
*/
const MASTER_SHEET_NAME = 'This is the master sheet';
/**
* Retrieves information for a specified sheet name
* @error Throws an error if the sheet name does not exist or if startRow is less than 1
* @param {string} sheetName The name of the sheet to retrieve
* @param {number} [startRow] The row number to start retrieval (1-based). Default: 1
* @returns {{ index: number, id: string, title: string, date: Date, sheet: SpreadsheetApp.Sheet }[]} Retrieval results
* - index: Row number (1-based)
* - id: ID in column A
* - title: Title in column B
* - date: Date entered in column C
* - sheet: Sheet object
*/
function getTable(sheetName, startRow = 1) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
if (!sheet)
throw Error(`Could not retrieve the sheet named "${sheetName}"`);
if (startRow == null || startRow < 1)
throw Error(`Invalid start row number: "${startRow}"`);
const cells = sheet
.getRange(startRow, 1, sheet.getLastRow(), sheet.getLastColumn())
.getValues();
return cells.map((row, index) => {
const [id, title, date] = row;
return {
index: index + startRow,
id,
title,
date: new Date(date),
sheet,
};
});
}
Pseudo-type definitions for JSDoc are also possible
Since GAS is based on JavaScript, you cannot define static types, but you can define type definitions in jsDoc and reuse them in other jsDocs. Let's define them so the editor can infer types more easily and to make writing jsDoc more convenient.
/**
* User Information
* @typedef {object} UserType
* @prop {string} id
* @prop {string} name
* @prop {number} age
* @prop {UserType[]} friends
* @prop {string} address The user's email address
* @prop {SpreadsheetApp.Sheet} mySheet A sheet generated for each user
*/
/**
* Get user information
* @param void
* @returns { UserType[] } Retrieved user information
*/
function getUsers() { ... }
function noticeUsers() {
const users = getUsers();
// Input completion works in the GAS editor
users. // forEach, map, filter, etc.
users[0]. // id, name, age, etc.
users[0].mySheet. // getRange, setName, getLastRow, etc.
}
// It's also possible to assign types to variables/constants using `@type`
/** @type { UserType } */
const user = {
id: 'd023jg',
name: 'Test user',
// ...
};
Turning common processes into libraries
Processes that are frequently used due to specific business requirements should be wrapped as functions and made reusable, rather than writing the same code repeatedly.
GAS has a mechanism called "Libraries" that allows you to call and use functions from other projects (GAS files). This enables efficient development without having to write the same logic twice across multiple projects.
// Process to retrieve team member information from the "TABLE" sheet of the linked spreadsheet
// You can use separate functions to include the executor's name or branch processing based on the executor, etc.
/**
* Get member information
* @param {string} address Address information of the target member
* @returns {object} Member information
* - {string} address Return of the address
* - {string} name Name (last name only)
* - {number} age Age
* - {Date} lastUpdated Last updated date
*/
function getMemberInfo(address) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('TABLE');
const values = sheet
.getDataRange()
.getValues()
.map((row) => {
let [address, name, age, lastUpdated] = row;
lastUpdated = new Date(lastUpdated);
return { address, name, age, lastUpdated };
});
const member = values.find((row) => row.address === address);
if (!member)
throw Error(`Member information not found. Address: ${address}`);
return member;
}
By deploying this as a library and loading it as "TeamLib" in another project, you can easily reuse the same logic!
function main() {
const me = Session.getActiveUser().getEmail();
const memberInfo = Teamlib.getMemberInfo(me);
console.log(`The current executor is "${memberInfo.name}"`);
// => The current executor is "Yamaguchi"
}
Please refer to other articles for how to create and use libraries.
Triggers are individually owned, and triggers from other accounts cannot be changed
The "trigger" feature in GAS is very convenient...!
It allows functions to start automatically based on time (cron) or events such as linked Forms or Spreadsheets.
Typical triggers include:
- Process every X minutes
- Process every day during a certain hour
- Process once at YYYY/MM/DD HH:mm
- Process when a form is submitted
- Process when a spreadsheet is opened
These are very useful features, but there is one point to be careful about during team development.
GAS has a philosophy that feels "closed to the individual," and triggers have the property that "only the person who set the trigger can edit or delete it."
Additionally, the executor of a function started by a trigger is the account of the person who set the trigger. Therefore, processes dependent on the account (all GmailApp tasks, Session.getActiveUser(), all access rights, etc.) are processed from the perspective of the trigger setter.
This property where "only the person who set the trigger can edit or delete it" means, in other words, that "triggers created by other members cannot be edited or deleted by anyone but the creator."
If there are account-dependent processes and the trigger administrator moves or leaves the company, the previous trigger remaining will cause issues. Therefore, handover of trigger administration is necessary.
Make sure to prepare a procedure manual to delete the previous administrator's triggers and create new ones for the new administrator during personnel changes!
That's all!
I've written this all out quite hurriedly, but I would be happy if even one of these points resonates with GAS developers and helps with your development!
Discussion