Export Formulas and Notes from a Google Spreadsheet – A Complete Guide

Google Sheets is an incredibly powerful tool for organizing, analyzing, and automating data workflows. While it’s common to export the values or content from a spreadsheet, there are times when you may need to export the underlying formulas or cell notes (comments/annotations) used within the spreadsheet.

Whether you’re documenting your work, creating a report, migrating a sheet, or building a spreadsheet auditing tool, exporting formulas and notes is extremely useful. In this detailed guide, you’ll learn how to extract and export both formulas and notes using Google Apps Script, and save them in a structured format such as a new spreadsheet or a downloadable CSV.

Why Export Formulas and Notes?

Here are a few reasons:

• Documentation: To keep a record of how your calculations work.

• Auditing: To verify how data is generated or calculated.

• Collaboration: To explain logic and comments to other users.

• Migration: To copy complex formulas into another file.

• Debugging: To identify and troubleshoot errors in formula logic.

Difference Between Formula, Value, and Note

• Cell Value: The actual result visible in a cell (e.g., 50).

• Formula: The expression used to calculate the value (e.g., =A1+B1).

• Note: A user-entered annotation that helps explain what the cell represents. (Not to be confused with Google Sheets comments.)

Google Apps Script lets you extract all of these programmatically.

How to Export Formulas and Notes Using Apps Script

We’ll create a script that scans all the sheets in a spreadsheet, extracts formulas and notes, and writes them into a new sheet or file for export.

Step 1: Open the Apps Script Editor

1. Open your target Google Spreadsheet.

2. Click on Extensions > Apps Script.

3. Delete any existing code in Code.gs.

Step 2: Paste the Script

Here’s a sample script that extracts formulas and notes from all sheets and exports them into a new tab called “Formula and Notes Export”:

function exportFormulasAndNotes() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = spreadsheet.getSheets();
  const output = [];

  output.push(['Sheet Name', 'Cell Address', 'Formula', 'Note', 'Value']);

  sheets.forEach(sheet => {
    const range = sheet.getDataRange();
    const formulas = range.getFormulas();
    const notes = range.getNotes();
    const values = range.getValues();
    const numRows = range.getNumRows();
    const numCols = range.getNumColumns();

    for (let row = 0; row < numRows; row++) {
      for (let col = 0; col < numCols; col++) {
        const formula = formulas[row][col];
        const note = notes[row][col];
        if (formula || note) {
          const cellAddress = range.getCell(row + 1, col + 1).getA1Notation();
          const value = values[row][col];
          output.push([
            sheet.getName(),
            cellAddress,
            formula || '',
            note || '',
            value
          ]);
        }
      }
    }
  });

  // Create new sheet or overwrite existing one
  let exportSheet = spreadsheet.getSheetByName("Formula and Notes Export");
  if (exportSheet) {
    spreadsheet.deleteSheet(exportSheet);
  }
  exportSheet = spreadsheet.insertSheet("Formula and Notes Export");

  exportSheet.getRange(1, 1, output.length, output[0].length).setValues(output);

  Logger.log("Export complete.");
}

How It Works

• Loops through each sheet in the current spreadsheet.

• Gets the full data range and collects:

  • .getFormulas() for formulas

  • .getNotes() for cell notes

  • .getValues() for reference values

• For each cell, it checks if there is a formula or note.

• If so, it saves:

  • Sheet name

  • Cell address (e.g., B2)

  • Formula (if any)

  • Note (if any)

  • Final value of the cell

The results are written into a new sheet called Formula and Notes Export.

Step 3: Run the Script

1. Save the script (Ctrl + S) with a name like ExportFormulasNotes.

2. Click the Run button ▶️ to execute.

3. If prompted, authorize permissions.

4. After a few seconds, you’ll see a new tab in your sheet called Formula and Notes Export.

Sample Output

Export to CSV (Optional)

You may want to export the output sheet as a downloadable CSV file.

Add the following function:

function exportSheetToCSV() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Formula and Notes Export");
  const range = sheet.getDataRange();
  const values = range.getValues();

  let csvContent = '';
  values.forEach(row => {
    const line = row.map(cell => `"${cell}"`).join(',');
    csvContent += line + '\n';
  });

  const blob = Utilities.newBlob(csvContent, 'text/csv', 'formula-notes-export.csv');
  const file = DriveApp.createFile(blob);

  Logger.log("CSV created: " + file.getUrl());
}

1. Run exportFormulasAndNotes() first.

2. Then run exportSheetToCSV() to generate a CSV file in your Google Drive.

3. Check the Logger or Google Drive for the file URL.

Filtering Options (Optional Enhancements)

You can modify the script to:

• Export only formulas (ignore notes)

• Export only notes (ignore formulas)

• Include timestamps

• Filter by sheet name or column

• Export to external spreadsheet instead of current one

Example – Export only notes:

if (note) {
  output.push([
    sheet.getName(),
    cellAddress,
    '',       // no formula
    note,
    value
  ]);
}

Use Cases

• Documentation: Create a technical reference for your spreadsheet models.

• Auditing: Verify spreadsheet logic in finance, accounting, or analytics reports.

• Support: Help collaborators understand how certain values are calculated.

• Training: Teach new team members about the sheet structure.

• Backup: Save formulas and notes before restructuring a complex workbook.

Troubleshooting Tips

• If the script fails with authorization errors, ensure you’ve allowed permissions when prompted.

• If you receive an error like Cannot read property of undefined, check for empty rows or ranges.

• Use Logger.log() to debug issues by logging formula, note, or range values.

Conclusion

Exporting formulas and notes from Google Sheets allows you to better manage, document, and understand the logic behind your spreadsheet models. With just a few lines of Apps Script, you can automate this process, saving time and reducing the risk of errors.

This method is particularly helpful for professionals who handle financial models, large datasets, or collaborative spreadsheets where clarity and auditability matter.

Whether you're a spreadsheet power user, educator, analyst, or developer—this tool gives you visibility into the how and why behind your data.