Writing a script to remove formulae in Google sheets

Question

I have a sheet of data that reads in information from another sheet throughout the day via a vlookup. The other sheet is linked to a website via an importxml which refreshed throughout the day.

Once the information has been obtained, i need to remove the formula and leave the values in place. I.e. Once the cell no longer reads as #N/A, replace that row of data with values. My idea is then to set this as a trigger to then run every hour, looping through each row and updating all the rows that no longer show #N/A.

Unknown Unknown · Accepted Answer · 2017-01-03T21:52:39

You can replace the output of a formula with static values by running the following script. The key point is that a certain range is copied to itself, but only as values.

function freezeValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');  // or whatever name
  var range = sheet.getRange("A1:Z1");    // assuming your data appears in A1-Z1 
  range.copyTo(range, {contentsOnly: true});
}

That said, it'd be more logical to keep the formula in place, so it continues to produce the output you wish to log, and instead copy its output elsewhere, for example to a new row of the spreadsheet. Example:

function logValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');  // or whatever name
  var range = sheet.getRange("A1:Z1");    // assuming your data appears in A1-Z1 
  var values = range.getValues();         // get the data
  sheet.appendRow(values[0]);   // appends a row with these values to the bottom of sheet
}

Writing a script to remove formulae in Google sheets

1 Answers