Export Formulas and Notes from a Google Spreadsheet

[CodeCanyon 315]

Dan Thareja has written a Google Script that will let you export all the formulas, cell values and notes from a Google Spreadsheet as a JSON file. You can also pull the Google formulas in another web app or Google Apps script using a GET Request. It should come handy when you are reviewing the formulas of a large sheet.

// Credit: github.com/danthareja

function doGet(request) {
  // ID of Google Spreadsheet
  var json = getNotesAndFormulas(request.parameter.id);

  return ContentService.createTextOutput(JSON.stringify(cache)).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 }
    );
}

View the full article