Spreadsheet count tracker
Objective: Embed a count of the number of spreadsheets I work on in a given year, month, and week
Background: inspired by the GitHub Activity Graphs, I wanted to to know how much i was actually contributing to in my spreadsheets. I also wanted a test project to get data from spreadsheets and put it into a webpage. I do a lot of spreadsheets but i dont do a lot of websites. I also don't do a lot of Web Apps
Tools used: Google Sheets, Apps Scripts
Method:
1) Look through google's API documentation to see what information an App Script can pull
2) Write app script to identify how many spreadsheets I've edited and write to a database sheet
3) Use FILTER functions to seperate out by week, month, and year. Put these COUNTS on a stats page
4) Write app script to look at these counts and publish as a web script
5) integrate into site
function logDailySpreadsheetActivity() {
const files = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
const activitySheet = SpreadsheetApp.getActive().getSheetByName('Activity');
const today = new Date();
const rows = [];
const myEmail = Session.getActiveUser().getEmail();
let count = 0;
while (files.hasNext()) {
const file = files.next();
count++;
let ownerEmail = '';
let ownedByMe = '';
try {
ownerEmail = file.getOwner().getEmail();
ownedByMe = (ownerEmail === myEmail) ? 'Yes' : 'No';
} catch (e) {
ownerEmail = 'No Owner Available';
ownedByMe = 'Unknown';
}
rows.push([
today, // A: date
'', // B: total sheets get calculated by a sequence function
file.getName(), // C: file name
ownerEmail, // D: owner email
ownedByMe, // E: owned By me - used because multiple of my google accounts contribute to this
file.getLastUpdated() // F: last updated
]);
}
if (rows.length > 0) {
rows[0][1] = count;
activitySheet.getRange(activitySheet.getLastRow() + 1, 1, rows.length, 6).setValues(rows);
}
}The code above is what queries the google drive and then prints to the activity page in the spreadsheet to the right. One of the major limitations of this script is it can only detect a binary "last edited" state to determine if a sheet has been worked on. it has no indication of the nature or complexity of the work involved. just that a cell was changed by me
function doGet() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stats");
const [year, month, week] = sheet.getRange("A2:C2").getValues()[0];
const stats = { year, month, week };
return ContentService.createTextOutput(JSON.stringify(stats)).setMimeType(ContentService.MimeType.JSON);
}this is the web app :) looks at A2:C2 and prints it to a JSON file that this website uses
and this is how the website calls them!