How can I convert all cells from formulas to plain text so I can export them as CSV?

Question

I have around 12 sheets in a Google Spreadsheets document where I have a bunch of formulas. I'd like to do one of two things:

1. I would love to just export these as CSVs. I don't want the values though, I want the actual formulas. I.e. instead of the cell value being 3, I would like it to be =B2+B4. I don't mind manually downloading each.

2. If that's not possible, is there some way to convert all the the formula cells to the formula itself?

I know that I can just put quotes around the formulas and they will become text strings which I can then export to CSV. The issue is, that's a lot of cells ... and I don't want to have to do that for all the cells.

Can you folks help me out with this?

Answer 1

The following script implements your second approach: convert all formula cells to the text displaying the formula. It does so in all sheets in the current spreadsheet.

The idea is simple: get all values (getValues) and formulas (getFormulas). If a cell contains a formula, then the new value is the formula text prepended by the apostrophe ' (which isn't displayed; it's just an indicator that the cell content should be treated as plain text).

After exporting the sheets as CSV (one by one, using the menu), you can restore the original state of the spreadsheet either by using revision history, or by running the same script again. (If the script runs again, a cell having text content =A1+1 will again become a formula =A1+1)

function formulasAsText() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var k = 0; k < sheets.length; k++) {
    var range = sheets[k].getDataRange();
    var values = range.getValues();
    var formulas = range.getFormulas();
    for (var i = 0; i < values.length; i++) {
      for (var j = 0; j < values[0].length; j++) {
        values[i][j] = formulas[i][j] ? "'" + formulas[i][j] : values[i][j];
      }
    }
    range.setValues(values);
  }
}