CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
lise
lise

Reputation: 163

Google Sheets Script to find formulas and copy/paste them into notes

I like to copy/paste as values all of my older sheets to save resources, but I don't want to remove the formulas as I rely on them to re-create them in the future (some are quite complicated). I thought a good alternative would be to copy each formula into its cell's note before I paste as values only, so I thought I could write a script to do this, preferably throughout an entire worksheet. I confess I'm googling as I go here, but am I on the right track? Do I have the right variables to start working on the loop? 

        function CopyFormulaToNote() {

        /* I dont' want to get just the active sheet but all of them
        look into how to do that */

      var ss=SpreadsheetApp.getActive().getActiveSheet();
      var lr=ss.getLastRow();
      var lc=ss.getLastColumn();
      var range=ss.getRange(1,1,lr,lc);

        /* Find the first formula in the entire spreadsheet
        I doubt it will just find all formulas per se without specifying that a formula begins with =
        although there is a getFormula function...
        and if I do have to specify that I think what I have below says to look for = as the first character */

      var search=range.createTextFinder().matchFormulaText("^[=]");  //search for formula ie first character =
      var result=search.getCurrentMatch();      //I *think* this would get the 1st result
      var cell=result.getCell(row, column);    //get the cell range of the result
      var note=cell.setNote(note);             //create a cell note for that cell



      /* and now create a loop to do it everywhere that includes */

result.CopyTo(note);  //copy the result into the note

    }

Upvotes: 0

Views: 482

Answers (1)

Tanaike
Tanaike

Reputation: 201613

  • You want to retrieve the formulas from all cells in all sheets in the Google Spreadsheet.
  • You want to put the retrieved formulas to each cell as the note.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

I thought that in your case, the script can be simpler by getFormulas() and setNotes().

Sample script:

function myFunction() {
  SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheets()
  .forEach(function(sheet) {
    var range = sheet.getDataRange();
    range.setNotes(range.getFormulas());
  });
}

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 3

Related Questions