DEV Community

ROUTE06, Inc. profile image Takashi Masuda
Takashi Masuda for ROUTE06, Inc.

Posted on

Continuously Storing Traffic Data of Multiple GitHub Repositories Using GAS

#github #googlesheets #googleappsscript

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.

ROUTE06, Inc.

Consideration of OSS Activity Metrics Based on GitHub Repository Data

Takashi Masuda for ROUTE06, Inc. ・ Dec 16

#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

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

Example of Google Sheet Creation

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

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

These commands use the following GitHub REST API endpoints:

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;
};
Enter fullscreen mode Exit fullscreen mode

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;
};
Enter fullscreen mode Exit fullscreen mode

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',
  },
];
Enter fullscreen mode Exit fullscreen mode

Relationship between COLLECTION_TARGETS and Google Sheet

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
Enter fullscreen mode Exit fullscreen mode

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);
};
Enter fullscreen mode Exit fullscreen mode

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/.

Top comments (0)

Read next

inusui profile image

Desplegar a Firebase con GitHub actions

Ricardo Domínguez González -

tejas_kumar_83c520d6bef27 profile image

It's Okay to Code on Nights and Weekends

Tejas Kumar -

matyuda profile image

re:Invent 2023に参加してから1年たってみて

Yasuhiro Matsuda -

ftahiri profile image

Stratégies de rendu Next.js: Optimiser performances & SEO en 2025

Fathellah TAHIRI -