Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dsernst/2e5e98eb8d492339c8d9 to your computer and use it in GitHub Desktop.
Save dsernst/2e5e98eb8d492339c8d9 to your computer and use it in GitHub Desktop.
// requires ?id=<GOOGLE_SHEET_ID>
// you must have authorized access to the spreadsheet
function doGet(request) {
if (!request.parameter.id) {
return ContentService.createTextOutput(JSON.stringify(new Error('no Google Sheet id set')))
.setMimeType(ContentService.MimeType.JSON);
}
var cache = getNotesAndFormulas(request.parameter.id);
// Return cache as JSON
return ContentService.createTextOutput(JSON.stringify(cache))
.setMimeType(ContentService.MimeType.JSON);
}
// requires ?data=<CACHED_OBJECT> where CACHED_OBJECT is the result of a previous doGet()
function doPost(request) {
if (!request.parameter.data) {
return ContentService.createTextOutput(JSON.stringify(new Error('no data sent')))
.setMimeType(ContentService.MimeType.JSON);
}
var result = setNotesAndFormulas(request.parameter.data);
return ContentService.createTextOutput(JSON.stringify(result))
.setMimeType(ContentService.MimeType.JSON);
}
function getNotesAndFormulas(spreadsheetId) {
return SpreadsheetApp
.openById(spreadsheetId)
.getSheets()
.reduce(function(cache, sheet) {
var sheetData = cache[sheet.getName()] = {};
var range = sheet.getDataRange();
sheetData.range = range.getA1Notation();
sheetData.notes = range.getNotes();
sheetData.formulas = range.getFormulas();
return cache;
}, { spreadsheetId: spreadsheetId });
}
function setNotesAndFormulas(cache) {
SpreadsheetApp
.openById(cache.spreadsheetId)
.getSheets()
.forEach(function(sheet) {
var range = cache[sheet.getName()].getDataRange();
range.setNotes(cache.notes);
range.setFormulas(cache.formulas);
});
return { message:'Cache successfully loaded!' };
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment