Hey developers!
In my last article, I discussed metrics related to OSS activity, specifically those with limited retention periods:
As mentioned above, views, unique views, and replication have only been available for the past two weeks. In the next issue we will show how we solve this problem.
This time, I’ll introduce a way to store these metrics for more than two weeks using Google Apps Script (GAS) and Google Sheets.
Create a Google Sheet
Create a Google Sheet, one for each repository, as shown below. For this post I set up the worksheet Giselle and Liam.
Traffic data can be viewed in the Insights tab under the Traffic sidebar for each repository. Here are some examples:
Entering data manually by referencing these charts is very tedious, so I recommend using command line interface.
Example: Retrieve pageviews 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: Retrieve clones and unique clones from 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:
Access tokens using GitHub Apps
An access token is required to use the GitHub API.
While using personal access tokens is simple, both classic and fine-grained tokens suffer from the following issues:
- Operational issues related to GitHub users
- Security risks associated with long-lived tokens
To solve these problems, I created a dedicated GitHub application and issued short-term access tokens to each GAS execution.
Build a GitHub application
Follow the official documentation to build the GitHub App and install it to the required repository.
🔗 Register a GitHub App – GitHub Documentation
Only the following permissions are required:
- Manage read-only
- Metadata read only
Since we won’t be using webhooks, uncheck the “Activities” box.
After creation, please also:
- Note down the application ID
- Generate a private key and download it locally
implement natural gas
Open the spreadsheet and click Extensions → Apps Script from the menu.
Create two documents and paste the following code into each document:
master file
// 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 = [
,
// 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 ,
];
const main = () => ;
/**
* Update Google Sheet with latest GitHub data
*
* @param
// 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 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 posting, update COLLECTION_TARGETS The constants in main.gs contain 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',
},
];
Configure script properties in GAS
Click “⚙️Project Settings” on the GAS sidebar to configure script properties.
GITHUB_APP_ID
Add a new script attribute named GITHUB_APP_ID and set its value to the Application ID you noted earlier.
GITHUB_APP_PRIVATE_KEY
Convert the downloaded private key to PKCS#1 arrive PKCS#8 Format, in accordance with GAS requirements. replace GITHUB.PRIVATE-KEY.pem and GAS.PRIVATE-KEY.pem with your profile 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 setKey Run it from the menu. Script properties GITHUB_APP_PRIVATE_KEY should be created. Once created, delete the above code.
💡 Note: If you set properties through “⚙️Project Settings”, Exception: Invalid argument: key An error occurred while running GAS. It seems that there is a problem with the way GAS handles newline codes. Even changing other script properties seems to require reconfiguring it.
Create triggers for plan execution
Click “🕓Trigger” on the GAS sidebar to set the schedule trigger:
- Select the function to run
- Select event source
- Select a time-based trigger type
- Choose a time of day
- Failure notification settings
With this setting, your spreadsheet will be automatically updated once a day. Errors will be notified to you via email.
in conclusion
This article introduces a storage method Views, Unique Views, Clonesand Unique Clones Google Sheets have a limited retention period:
- Centrally manage traffic data from multiple repositories
- Data stored for more than two weeks
- Reduce operational burden through automation
- Secure authentication with the GitHub app
I hope you find this helpful.
Extra: About Gisele and Liam as OSS
this Giselle Repositories mentioned in Creating a Google Sheet Giselle is a platform that leverages generative AI to create agents and workflows without code. Details can be found on the Giselle Services website: https://giselles.ai/.
this Liam Headquarters Repository is Liam’s tool that makes it easy to generate beautiful and easy-to-read ER diagrams. Details can be found on the Liam Services website: https://liambx.com/.