Continuously Storing Traffic Data of Multiple GitHub Repositories Using GAS

Hey Devs!

In my previous post, I discussed metrics related to OSS activities, particularly those with limited retention periods:

As mentioned above, Views, Unique views, and Clones are only available for the last two weeks. In the next issue, we will show how we solved this problem.

Consideration of OSS Activity Metrics Based on GitHub Repository Data

Takashi Masuda for ROUTE06, Inc. ・ Dec 16 '24

#ospo #opensource #github #marketing

This time, I'll introduce a method to store these metrics for more than two weeks using Google Apps Script (GAS) and Google Sheets.

• Creating a Google Sheet
• Using a GitHub App Access Token
• Creating a GitHub App
• Implementing GAS
  • main.gs
  • github.gs
• Configuring Script Properties in GAS
  • GITHUB_APP_ID
  • GITHUB_APP_PRIVATE_KEY
• Creating a Trigger for Scheduled Execution
• Conclusion
• Supplement: About Giselle and Liam as OSS

Creating a Google Sheet

Create a Google Sheet with one sheet per repository, as shown below. For this post, I created sheets for giselle and liam.

Traffic data can be viewed in the Insights tab under the Traffic sidebar for each repository. Here are some examples:

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

Manually inputting data by referencing these graphs is tedious, so I recommend using the gh CLI.

Example: Retrieving Views and Unique Visitors for the past two weeks:

$ gh api -H "Accept: application/vnd.github.v3.star+json" \
   /repos/giselles-ai/giselle/traffic/views \
  | jq -r '["Date", "Views", "Unique visitors"],(.views[] | [.timestamp, .count, .uniques]) | @csv' \
  | sed -e 's/T00:00:00Z//g'
"Date","Views","Unique visitors"
"2024-12-03",33,7
"2024-12-04",273,17
(snip)

Example: Retrieving Clones and Unique Cloners for the past two weeks:

$ gh api -H "Accept: application/vnd.github.v3.star+json" \
   /repos/giselles-ai/giselle/traffic/clones \
  | jq -r '["Date", "Clones", "Unique cloners"],(.clones[] | [.timestamp, .count, .uniques]) | @csv' \
  | sed -e 's/T00:00:00Z//g'
"Date","Clones","Unique cloners"
"2024-12-03",12,5
"2024-12-04",148,12
(snip)

These commands use the following GitHub REST API endpoints:

• Get page views | REST API endpoints for repository traffic - GitHub Doc
• Get repository clones | REST API endpoints for repository traffic - GitHub Doc

Using a GitHub App Access Token

An Access Token is required to use the GitHub API.

While using a Personal Access Token is straightforward, both Classic and Fine-grained tokens have the following issues:

• The operational problems associated with a GitHub user
• The security risks associated with a long-lived token

To address these, I created a dedicated GitHub App and issued short-lived Access Tokens for each GAS execution.

Creating a GitHub App

Follow the official documentation to create a GitHub App and install it to the required repositories.

🔗 Registering a GitHub App - GitHub Docs

Only the following permissions are needed:

• Administration Read-only
• Metadata Read-only

Since we won't use Webhooks, uncheck the "Active" box.

After you have completed the creation, please also:

• Note the App ID
• Generate a Private Key and download it locally

Implementing GAS

Open the spreadsheet and click "Extensions" → "Apps Script" from the menu.

Create two files and paste the following code into each:

main.gs

// Copyright (c) 2024 ROUTE06, Inc.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     https://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

const COLLECTION_TARGETS = [
  {
    repo: 'giselles-ai/giselle',
    sheetName: 'giselle',
    viewsDateColumn: 'A',
    clonesDateColumn: 'E',
  },
  {
    repo: 'liam-hq/liam',
    sheetName: 'liam',
    viewsDateColumn: 'A',
    clonesDateColumn: 'E',
  },
];

const main = () => {
  COLLECTION_TARGETS.forEach(updateSheetWithLatestData);
};

/**
 * Update Google Sheet with latest GitHub data
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @param {string} sheetName - (e.g., 'giselle')
 * @param {string} viewsDateColumn - (e.g., 'A')
 * @param {string} clonesDateColumn - (e.g., 'E')
 * @return {void}
 */
const updateSheetWithLatestData = ({repo = undefined, sheetName = undefined, viewsDateColumn = undefined, clonesDateColumn = undefined}) => {
  updateSheetWithLatestTrafficViews({repo, sheetName, column: viewsDateColumn});
  updateSheetWithLatestTrafficClones({repo, sheetName, column: clonesDateColumn});
};

/**
 * Update Google Sheet with latest traffic views
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @param {string} sheetName - (e.g., 'giselle')
 * @param {string} column - (e.g., 'A')
 * @return {void}
 */
const updateSheetWithLatestTrafficViews = ({repo = undefined, sheetName = undefined, column = undefined}) => {
  const trafficViews = GitHubGetTrafficViews({repo});
  const converted = convertTimestampToDate(trafficViews.views);
  updateSheetWithLatestCore({actualData: converted, sheetName, column})
};

/**
 * Update Google Sheet with latest traffic clones
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @param {string} sheetName - (e.g., 'giselle')
 * @param {string} column - (e.g., 'E')
 * @return {void}
 */
const updateSheetWithLatestTrafficClones = ({repo = undefined, sheetName = undefined, column = undefined}) => {
  const trafficClones = GitHubGetTrafficClones({repo});
  const converted = convertTimestampToDate(trafficClones.clones);
  updateSheetWithLatestCore({actualData: converted, sheetName, column})
};

/**
 * Update Google Sheet with the data passed as argument
 *
 * @param {Array.<{date: Date, count: number, uniques: number}>} actualData
 * @param {string} sheetName - (e.g., 'giselle')
 * @param {string} column - (e.g., 'E')
 * @return {void}
 */
const updateSheetWithLatestCore = ({actualData = undefined, sheetName = undefined, column = undefined}) => {
  const earliestDate = getEarliestDate(actualData);
  const blankData = buildBlankData(earliestDate);
  const completeData = mergeActualAndBlank(actualData, blankData);

  let curDateCell = vlookupWithDate({sheetName, column, targetDate: earliestDate});

  completeData.forEach((e) => {
    const formattedDate = e.date.toISOString().split('T')[0]; // YYYY-MM-DD
    const curCountCell = getCountCell(curDateCell);
    const curUniquesCell = getUniquesCell(curDateCell);

    console.log(`[Write] ${curDateCell.getA1Notation()}: ${formattedDate},  ${curCountCell.getA1Notation()}: ${e.count},  ${curUniquesCell.getA1Notation()}: ${e.uniques}`);

    curDateCell.setValue(formattedDate);
    curCountCell.setValue(e.count);
    curUniquesCell.setValue(e.uniques);

    curDateCell = getNextDateCell(curDateCell);
  });
};

class DateNotFoundError extends Error {}

/**
 * Searches the specified column vertically and returns cell names matching the specified date
 *
 * @param {string} sheetName - (e.g., 'giselle')
 * @param {string} column - (e.g., 'A')
 * @param {Date} targetDate
 * @return {Range} - (e.g., the range of 'A31')
 */
const vlookupWithDate = ({sheetName = undefined, column = undefined, targetDate = undefined}) => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange(`${column}:${column}`); // Get the entire column range
  const values = range.getValues();

  const rowIndex = values.findIndex(row => row[0] instanceof Date && row[0].toDateString() === targetDate.toDateString());

  if (rowIndex == -1) {
    throw new DateNotFoundError;
  }

  return sheet.getRange(`${column}${rowIndex + 1}`);
};

const getCountCell = dateCell => dateCell.offset(0, 1);
const getUniquesCell = dateCell => dateCell.offset(0, 2);
const getNextDateCell = dateCell => dateCell.offset(1, 0);


/**
 * Convet timestamp to date
 *
 * @param {Array.<{timestamp: string, count: number, uniques: number}>} data
 * @return {Array.<{date: Date, count: number, uniques: number}>}
 */
const convertTimestampToDate = data => {
  return data.map(item => ({
    date: new Date(item.timestamp),
    count: item.count,
    uniques: item.uniques,
  }));
};

/**
 * Merge actual data and blank data
 *
 * @param {Array.<{date: Date, count: number, uniques: number}>} actual
 * @param {Array.<{date: Date, count: 0, uniques: 0}>} blank
 * @return {Array.<{date: Date, count: number, uniques: number}>}
 */
const mergeActualAndBlank = (actual, blank) => {
  return blank.map(blankItem => {
    // Find data matching date in `actual`
    const actualItem = actual.find(a => a.date.toDateString() === blankItem.date.toDateString());
    // If `actual` data is available, it is given priority; otherwise, `blank` data is used.
    return actualItem || blankItem;
  });
};

/**
 * Get earliest date
 *
 * @param {Array.<{date: Date, count: number, uniques: number}>} data
 * @return {Date}
 */
const getEarliestDate = data => {
  return new Date(
    data.reduce(
      (first, current) => current.date < first ? current.date : first,
      data[0].date
    )
  );
};

/**
 * Build blank data
 *
 * @param {Date} inStartDate
 * @return {Array.<{date: Date, count: 0, uniques: 0}>}
 */
const buildBlankData = inStartDate => {
  const result = [];
  const today = new Date();
  const startDate = new Date(inStartDate); // Don't let the argument values change

  for (let i = startDate; i < today; i.setDate(i.getDate() + 1)) {
    result.push({ date: new Date(i), count: 0, uniques: 0 });
  }

  return result;
};

github.gs

// Copyright (c) 2024 ROUTE06, Inc.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     https://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

const GITHUB_APP_ID = PropertiesService.getScriptProperties().getProperty('GITHUB_APP_ID');
const GITHUB_APP_PRIVATE_KEY = PropertiesService.getScriptProperties().getProperty('GITHUB_APP_PRIVATE_KEY');

/**
 * Get traffic views
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @return {Object}
 * @see https://docs.github.com/rest/metrics/traffic?apiVersion=2022-11-28#get-page-views
 */
const GitHubGetTrafficViews = ({repo = undefined}) => {
  return gitHubApiGet({
    repo: repo,
    path: `/repos/${repo}/traffic/views`,
  });
};

/**
 * Get traffic clones
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @return {Object}
 * @see https://docs.github.com/rest/metrics/traffic?apiVersion=2022-11-28#get-repository-clones
 */
const GitHubGetTrafficClones = ({repo = undefined}) => {
  return gitHubApiGet({
    repo: repo,
    path: `/repos/${repo}/traffic/clones`,
  });
};

/**
 * Call [GET] GitHub API
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @param {string} path - the API path (e.g., /repos/giselles-ai/giselle/traffic/views)
 * @return {Object}
 */
const gitHubApiGet = ({repo = undefined, path = undefined}) => {
  const token = createGitHubAppToken(repo);

  const response = UrlFetchApp.fetch(
    `https://api.github.com${path}`,
    {
      method: 'GET',
      headers: {
        'Accept': 'application/vnd.github+json',
        'Authorization': `token ${token}`,
        'X-GitHub-Api-Version': '2022-11-28',
      },
    },
  );

  return JSON.parse(response);
};

/**
 * Create GitHub App installation access token
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @return {string}
 * @see https://docs.github.com/apps/creating-github-apps/authenticating-with-a-github-app/generating-an-installation-access-token-for-a-github-app
 * @see https://docs.github.com/rest/apps/apps?apiVersion=2022-11-28#create-an-installation-access-token-for-an-app
 * @note Use Closure to cache the App Tokens by repo
 */
const createGitHubAppToken = (() => {
  const tokenCache = new Map();

  return repo => {
    if (tokenCache.has(repo)) {
      console.log(`Hit the cache for the GitHub App Token for repo ${repo} `);
      return tokenCache.get(repo);
    }

    const jwt = createJWT({
      app_id: GITHUB_APP_ID,
      private_key: GITHUB_APP_PRIVATE_KEY,
    });

    const installationID = getGitHubAppInstallationID({repo, jwt});
    console.log(`repo: ${repo}, installationID: ${installationID}`);

    const response = UrlFetchApp.fetch(
      `https://api.github.com/app/installations/${installationID}/access_tokens`,
      {
        method: 'POST',
        headers: {
          'Accept': 'application/vnd.github+json',
          'Authorization': `Bearer ${jwt}`,
          'X-GitHub-Api-Version': '2022-11-28',
        }
      },
    );

    const token = JSON.parse(response.getContentText()).token;
    tokenCache.set(repo, token);
    console.log(`Cached GitHub App Token for repo ${repo}`);

    return token;
  };
})();

/**
 * Create JWT
 *
 * @param {string} app_id - GitHub App ID
 * @param {string} private_key - GitHub App private key
 * @return {string}
 * @see https://docs.github.com/apps/creating-github-apps/authenticating-with-a-github-app/generating-a-json-web-token-jwt-for-a-github-app
 */
const createJWT = ({app_id = undefined, private_key = undefined}) => {
  const now = Math.floor(new Date().getTime() / 1000);
  const iat = now - 60;  // Issues 60 seconds in the past
  const exp = now + 600; // Expires 10 minutes in the future

  const headerJSON = {
    typ: 'JWT',
    alg: 'RS256',
  };
  const header = Utilities.base64EncodeWebSafe(JSON.stringify(headerJSON));

  const payloadJSON = {
    iat: iat,
    exp: exp,
    iss: app_id,
  };
  const payload = Utilities.base64EncodeWebSafe(JSON.stringify(payloadJSON));

  const headerPayload = `${header}.${payload}`;
  const signature = Utilities.base64EncodeWebSafe(Utilities.computeRsaSha256Signature(headerPayload, private_key));

  return `${headerPayload}.${signature}`;
};

/**
 * Get a repository installation ID for the authenticated app
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @param {string} jwt
 * @return {string}
 * @see https://docs.github.com/rest/apps/apps?apiVersion=2022-11-28#get-a-repository-installation-for-the-authenticated-app
 */
const getGitHubAppInstallationID = ({repo = undefined, jwt = undefined}) => {
  const response = UrlFetchApp.fetch(
    `https://api.github.com/repos/${repo}/installation`,
    {
      method: 'GET',
      headers: {
        'Accept': 'application/vnd.github+json',
        'Authorization': `Bearer ${jwt}`,
        'X-GitHub-Api-Version': '2022-11-28',
      }
    },
  );

  return JSON.parse(response.getContentText()).id;
};

After pasting, update the COLLECTION_TARGETS constant in main.gs with your information:

const COLLECTION_TARGETS = [
  {
    repo: 'giselles-ai/giselle',
    sheetName: 'giselle',
    viewsDateColumn: 'A',
    clonesDateColumn: 'E',
  },
  {
    repo: 'liam-hq/liam',
    sheetName: 'liam',
    viewsDateColumn: 'A',
    clonesDateColumn: 'E',
  },
];

Configuring Script Properties in GAS

Click "⚙️Project Settings" on the GAS sidebar to configure script properties.

GITHUB_APP_ID

Add a script property named GITHUB_APP_ID and set its value to the previously noted the App ID.

GITHUB_APP_PRIVATE_KEY

Convert the downloaded Private Key from PKCS#1 to PKCS#8 format, as required by GAS. Replace GITHUB.PRIVATE-KEY.pem and GAS.PRIVATE-KEY.pem with your file name.

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

Next, temporarily create the following code in GAS.

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);
};

And select the setKey function from the menu and run it. The script property GITHUB_APP_PRIVATE_KEY should be created. Once created, delete the above code.

💡 Note: If you set the property via "⚙️Project Settings", an Exception: Invalid argument: key error occurs when running GAS. It seems to be a problem with how GAS handles line break codes. And it seems that you need to reconfigure it even if you change other script properties.

Creating a Trigger for Scheduled Execution

Click "🕓Triggers" on the GAS sidebar to set up a scheduled trigger:

• Choose which function to run
  • main
• Select event source
  • Time-driven
• Select type of time based trigger
  • Day timer
• Select time of day
  • e.g., 9am to 10am
• Failure notification settings
  • Notify me immediately

With this setup, your spreadsheet will automatically update once a day. Errors will be notified you via email.

Conclusion

This post introduced a method to store Views, Unique Views, Clones, and Unique Clones with limited retention periods into Google Sheets:

• Centralized management of Traffic data for multiple repositories
• Store of data beyond two weeks
• Reduced operational burden through automation
• Secure authentication via a GitHub App

I hope you find this helpful.

Supplement: About Giselle and Liam as OSS

The giselles-ai/giselle repository mentioned in the Creating a Google Sheet is for Giselle, a platform that enables no-code creation of agents and workflows utilizing generative AI. Detailed information can be found on the Giselle service website: https://giselles.ai/.

The liam-hq/liam repository is for Liam, a tool that effortlessly generates beautiful and easy-to-read ER diagrams. Detailed information can be found on the Liam service website: https://liambx.com/.