OSPO Google Apps Script by ROUTE06, Inc.

Overview

A Google Apps Script (GAS) that collects and stores various GitHub repository metrics in a Google Spreadsheet.

Metrics name	GitHub API
Stargazers	List stargazers
Traffic views	Get traffic views
Traffic clones	Get traffic clones
Traffic referrers	Get top referral sources

As an example, this script collects metrics from the following fictitious repositories:

• https://github.com/org1/repo1
• https://github.com/org2/repo2

Each repository corresponds to the following directories:

• repo1/
• repo2/

Initial Setup

1. Creating a GitHub App

1. Follow Registering a GitHub App to create a GitHub App
   • WebHook:
     • Uncheck the "Active" option
   • Permissions:
     • Administration Read-only
     • Metadata Read-only
2. Take note of the App ID
3. In the "General" settings, under "Private keys" generate a Private Key and download it to your local environment

2. Setting up clasp

Manage GAS code using @google/clasp.

1. Enable the Google Apps Script API at https://script.google.com/home/usersettings

2. Install clasp:

   npm i

3. Log in to script.google.com using clasp:

   npx clasp login

3. Creating a Google Spreadsheet and GAS Project

1. Navigate to the working directory:

   cd repo1

2. Create a Google Spreadsheet corresponding to org1/repo1 and and a GAS project associated with it:

   npx clasp create --type sheets --title "Repo1 repo metrics"

3. Check the files to be pushed to the GAS project:

   npx clasp status

4. Push local files to the GAS project:

   npx clasp push

5. In the GAS side menu, click "Project Settings" and set the correct time zone

6. Reflect the changes in appsscript.json:

   npx clasp pull

Tip

If developing in a team, remove the rootDir field from .clasp.json. The default value is the current directory.

Tip

Since .clasp.json and appsscript.json do not contain sensitive information, you can commit to git.

4. Configuring GAS Script Properties

In the GAS side menu, click "Project Settings" and create the following script properties:

• GITHUB_APP_ID:
  • The previously noted App ID
• GITHUB_APP_PRIVATE_KEY:
  • The previously downloaded Private key
  • Apply the workaround mentioned in the "Supplementary Information" section

5. Creating Sheets in the Google Spreadsheet

Create the sheets specified in main.js:

• TARGET_STARGAZERS.sheetName
• TARGET_VIEWS.sheetName
• TARGET_CLONES.sheetName
• TARGET_REFERRERS.sheetName

6. Verifying Functionality

Run the main function and confirm that the metrics are written to the respective sheets.

7. Setting Up Triggers

In the GAS side menu, click "Triggers" and set up periodic execution of the main function.

Deployment

To verify:

make status

To deploy:

make deploy

Supplementary Information: Workaround for GITHUB_APP_PRIVATE_KEY Script Property

Workaround 1

When setting the private key downloaded from GitHub via the GAS project UI and executing GAS, the error Exception: Invalid argument: key may occur.

This happens because the private key from GitHub is in PKCS#1 format, whereas GAS requires PKCS#8 format.

Convert the private key using the following command:

openssl pkcs8 -topk8 -inform PEM -outform PEM -in GITHUB.PRIVATE-KEY.pem -out GAS.PRIVATE-KEY.pem -nocrypt

Workaround 2

Even after converting the key using Workaround 1, the Exception: Invalid argument: key error may persist.

This is due to an issue in GAS with handling properties containing line breaks.

To resolve this, temporarily paste the following code into the GAS project and execute the setKey function to properly configure the GITHUB_APP_PRIVATE_KEY script property:

const TMP_PRIVATE_KEY = `
Paste the contents of GAS.PRIVATE-KEY.pem here
`;

const setKey = () => {
  PropertiesService.getScriptProperties().setProperty('GITHUB_APP_PRIVATE_KEY', TMP_PRIVATE_KEY);
};

If any other script properties are modified, the above reconfiguration may be necessary again.

References

• GitHub Apps の Access Token を取得するGoogle Apps Script 用ライブラリーを作った (In Japanese)
• Exception: Invalid argument: key · Issue #122 · googleworkspace/apps-script-oauth2

Contributing

Refer to our contribution guidelines and Code of Conduct for contributors.

References

Currently, we use this to collect metrics for the following repositories:

• https://github.com/giselles-ai/giselle
• https://github.com/liam-hq/liam