使用Google App Script和Google Sheet自动生成数据仪表盘

https://api.github.com/:owner/:repo

function updateGithubSheet(){
  // Make a request to GitHub's rest API and get the number of star gazers
  var restUrl = 'https://api.github.com/repos/lucidsoftware/xtract';
  var data = makeJsonRequest(restUrl)
  var numStarGazers = data['stargazers_count'];
  // Write to cell A1 of the active sheet
  var activeSheet = SpreadsheetApp.getActiveSheet();
  activeSheet.getRange("A2").setValue(numStarGazers);
}
function makeJsonRequest(url){
  var response = UrlFetchApp.fetch(url);
  return JSON.parse(response.getAs('application/json').getDataAsString());
}

function updateGithubSheet(){
  ...
  var activeSheet = SpreadsheetApp.getActiveSheet();
  addRow(activeSheet, numStarGazers);
}
function addRow(sheet, numStarGazers){
  var lastRow = sheet.getLastRow();
  var nextRow = lastRow + 1;
  var column = 1;
  sheet.getRange(nextRow, 1).setValue(new Date());
  sheet.getRange(nextRow, column).setValue(numStarGazers);
}

Contract: GITHUB
The Sheet will:
Have the Github Username in field B2.
Have the Github Repository in field B3.
Have row 4 starting at column B contain the Field Names in the response JSON. The Field Names terminate on the first column with an empty row 4.
The Script will:
Add one row after the last row on the sheet.
The new row will have the timestamp in column 1 followed by the Field Values corresponding to the Field Names from row 4.

function updateAllSpreadsheets(){
 // Go through each spreadsheet and check for the key
  SpreadsheetApp.getActive().getSheets().map(function (sheet){
    var sheetKey = sheet.getRange("A1").getValue();
    if (sheetKey === "GITHUB"){
      updateGithubSheet(sheet);
    }
    // Route to other keys here
  });
}

function updateGithubSheet(sheet){
  // Make a request to GitHub's rest API
  var userName = sheet.getRange("B2").getValue();
  var repoName = sheet.getRange("B3").getValue();
  var restUrl = 'https://api.github.com/repos/' + userName + "/" + repoName;
  var data = makeJsonRequest(restUrl)
  ...
}

function updateGithubSheet(sheet){
  ...
  var data = makeJsonRequest(restUrl)
  var fieldValues = getFieldValues(sheet, 4, 2, data);
  ...
}
function getFieldValues(sheet, fieldRow, startColumn, data){
  var fieldValues = [];
  var offset = 0;
  while(!sheet.getRange(fieldRow,startColumn + offset).isBlank()){
    var fieldKey = sheet.getRange(fieldRow, startColumn + offset).getValue();
    fieldValues[offset] = data[fieldKey];
    offset = offset + 1;
  }
  return fieldValues;
}

function updateGithubSheet(sheet){
  …
  var fieldValues = getFieldValues(sheet, 4, 2, data);
  addRow(sheet, fieldValues, 2);
}
function addRow(sheet, fieldValuesArray, startColumn){
  var lastRow = sheet.getLastRow();
  var nextRow = lastRow + 1;
  sheet.getRange(nextRow, 1).setValue(new Date());
  fieldValuesArray.forEach(function(fieldValue, offset) {
    sheet.getRange(nextRow, startColumn + offset).setValue(fieldValue);
  });
}

=IFERROR(
 FILTER(XTRACT_SUBSCRIBERS_COLUMN,
   ROW(XTRACT_SUBSCRIBERS_COLUMN) = 
     MAX(FILTER(ROW(XTRACT_SUBSCRIBERS_COLUMN),
          ISNUMBER(XTRACT_SUBSCRIBERS_COLUMN),
          XTRACT_DATES_COLUMN<FIRST_DAY_OF_NEXT_MONTH, XTRACT_DATES_COLUMN>=FIRST_DAY_OF_MONTH
        )))
, IF(FIRST_XTRACT_DATE>=FIRST_MONTH,0,))