A tiny abstraction layer over Google Apps Script Sheets API
Want to work with Google Spreadsheet Data but keep getting fooled by the API? Don't get fooled again!
This example assumes a tabular format for spreadsheet data with column headers in the first row. Column headers assumed here are band_name and rating.
// Grab a sheet to pass to SheetsData
var ss = SpreadsheetApp.getActiveSpreadsheet();
var recordsSheet = ss.getSheetByName("records");
// Instantiate a new instance and grab all the data from the sheet
var records = new SheetsData(recordsSheet);
var allMyRecords = records.getAll();
// Get all the records by The Who
var recordsByTheWho = allMyRecords.filter( function(record) {
return record.get("band_name") == "The Who";
});
// Update the rating for each record by The Who to 5
recordsByTheWho.forEach( function(record) {
record.set("rating", 5);
});
// Rock out 🎸var records = new SheetsData(recordsSheet);SheetsData converts plain-old-JavaScript objects down to a new row when object properties match column headers.
records.add({
"band_name": "Yes",
"rating": 4
});var allMyRecords = records.getAll()// Assumes allMyRecords call from above
allMyRecords[3].get("band_name");// Assumes allMyRecords call from above
allMyRecords[3].set("band_name", "Red Hot Chili Peppers");// Grab a sheet to pass to SheetsData
var ss = SpreadsheetApp.getActiveSpreadsheet();
var recordsSheet = ss.getSheetByName("records");
// Instantiate a new instance and grab all the data from the sheet
var records = new SheetsData(recordsSheet);
var arbitraryRow = 3;
var arbitraryColumn = 4;
records.store(arbitraryRow, arbitraryColumn, "Pinball Wizard");Get and set data from Google Sheets via a simple interface:
record.get(someColumnName);
record.set(someColumnName, newValue);Use that handy format to map/filter/reduce over the returned data and do stuff.
The code is written in ES5 due to the limitations of the Google Apps Script Environment. It also needs some cleanup and refactoring.