Google Sheets Script Endpoint

OpenEugene / OpenEugeneFestivalWeb

The Web site for the Open Eugene Festival

MIT License

3 stars 2 forks source link

Google Sheets Script Endpoint #47

Open mckelveygreg opened 5 years ago

mckelveygreg commented 5 years ago

You can attach a "google script" to a google sheet to turn your info into a REST API. All that needs to happen is the creator of the sheet needs to attach the script (Tools > Script Editor), and then publish the script (Publish > Deploy as Web App). Gatsby would then use that url to fetch the json. Therefore, on each build, Gatsby would grab the latest info from the sheet.

This replaces exporting json from sheets, adding that file to the repo, and then doing another site build.

Needs:

[ ] Vet the script (see next comment)
[ ] Add script to each sheet and publish (@nohorse has to do this)
[ ] Update all pages that call for json data
[ ] Cross fingers and hope to die

mckelveygreg commented 5 years ago

Code.gs

/**
 * @OnlyCurrentDoc
 */

// Function that is exposed
function doGet(e) {
    var output = getOutputObjectFromSheet(e.parameter);
    return ContentService.createTextOutput(JSON.stringify(output));
}

function getOutputObjectFromSheet(params) {
  // Inspired by: http://ramblings.mcpher.com/Home/excelquirks/gassnips/namedcolumns
  // get all the data on the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();

  // separete the headeres and data
  var headers = values[0];
  var data = values.slice(1);

  // Build result as an array of objects
  var result = []
  data.forEach(function(d,i) {
    var object = {};
    headers.forEach(function(h, j) {
      object[h] = d[j];
    });
    result.push(object);
  });

  return result;
}