Google-sheets – Can you remove formulas in bulk from Google Sheets

Here is an example of how to do this. When "update" or "edit" is selected from the dropdown boxes, data from the other, designated tab's rows are copied into the current sheet.

Here is the script:

function onEdit(e) {

////UPDATE ROWS WITH DATA FROM REMOTE SHEET VIA SCRIPT
///the script below is adapted from https://mashe.hawksey.info/2015/03/google-sheets-move-a-row-of-data-to-another-sheet-based-on-cell-value/
  // see Sheet event objects docs
  // https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
var ss = e.source;
var s = ss.getSheetByName("Animals List");
var r = e.range;
var remoteSheet = ss.getSheetByName("List B");

  // to let you modify where the action and move columns are in the form responses sheet
var actionCol = 1;

  // Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
  // Get the number of columns in the active sheet.
  // -1 to drop our action/status column
var colNumber = s.getLastColumn()-1;

var curNumberCell = s.getRange(rowIndex,2, 1, 1);
var curIDNumber =  curNumberCell.getValue();
var curDCell = s.getRange(rowIndex,4, 1, 1);
var curECell = s.getRange(rowIndex,5, 1, 1);

 // if our action/status col is changed to update or edit, do stuff
if (e.value == "update" || "edit" && colIndex == actionCol) {

 //THE FOLLOWING REPLACES THE FORMULAS OF THE ROW WITH THEIR GENERATED VALUES - UNCOMMENT TO USE:
 //var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
 //sourceRange.setValues(sourceRange.getValues());


///Retrieve data from remote tab/sheet that has matching cell value
var values = remoteSheet.getDataRange().getValues(); //IN REMOTE SHEET WE NEED TO SEARCH ENTIRE SHEET

for (var i = 0; i < values.length; i++) {
   if (values[i][0] == curIDNumber )
   {
   curDCell.setValue(values[i][1] );
   curECell.setValue(values[i][2] );
   }
   }    
} //end retrieve remote data
}

The following formula refers to the transpose of the current cell:

=indirect("R"&column()&"C"&row(), False)

Indeed, it takes the row and column numbers of the current cell and swaps them to get a cell reference in R1C1 format.

However, having to paste this in every row of upper-half of the matrix separately is not appealing. (One can't select a triangular region to put the formula in). So here is a better way:

Suppose your current matrix is in the sheet named "matrix". In cell A1 of another sheet, enter

=if(isblank(matrix!A1), indirect("matrix!R"&column()&"C"&row(), False), matrix!A1)

and extend this formula to a square region the size of the matrix. You get a symmetric matrix in that sheet: the formula takes either the matching cell in sheet "matrix" or its transpose, whichever is nonempty. Then select the matrix and copy values only (Ctrl-Shift-V) back into the original sheet.

Alternatively (if you don't want to mess with Indirect, maybe because your matrix is off-center and you'd need some adjustments to row/column numbers), use three sheets: one for lower half matrix, one for its transpose, and one more to combine them:

 =if(isblank(matrix!A1), transpose!A1, matrix!A1)