4

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?

2

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);
  }
}
  • Thanks so much for this! Works great. Accepting this answer now. I have a follow up question. Turns out that some of the cells are actually not formulas ... but just links. I have no idea how to turn a link into a hyperlink formula. Here's the question if you happen to have a few minutes / know what to do. Thanks again! webapps.stackexchange.com/questions/97285/… – Ringo Blancke Aug 6 '16 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.