cacheGoogleSheetNotesAndFormulas.js

// 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!' };
}