How can I set a default value for cells in Google Sheets?

Question

In my Google Sheets spreadsheet, I have a named data range called "scores". I would like for each cell in this data range to be assigned a default formula, which takes data from another sheet. However, if the user wants to type in manual data, they should be able to. If the cell becomes blank, it should reinstate the default formula.

I looked around, and all I could find are people saying to use two cells.

This will not work for my situation, so what is the simplest way to accomplish this?

Answer 1

I was able to create a script to do what I needed.

I used the onEdit function provided by Google Sheets, so the script runs whenever a cell is edited.

In order to make the script run as efficiently as possible, I check first if the cell is in a sheet in which the cells should be replaced with the default formula if empty. If it is not, the script exits. Then, I check if it is in the right range, and finally if it is not blank. If it meets all the criteria, the cell's value is replaced with the default formula. Here is the script:

function onEdit(e) {

  var editRange = { // B3:AL13
    top : 3,
    bottom : 12,
    left : 2,
    right : 38
  };

  //Get sheet from which the event occured
  var range = e.range
  var sheet = range.getSheet();  
  var name = sheet.getName();

  //Exit if sheet is not a sheet where cells should be changed
  if (name != "Section 1 Homework") return; 

  // Exit if we're out of range
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;

  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;

  //Exit if the cell is not blank
  if (!range.isBlank()) return;

  //Set the cell value to the default formula
  range.setValue( "=if(INDEX('Section 1 Attendance'!A1:AL12, " + thisRow + ", " + thisCol + ") = \"A\", \"A\", \"\")" )
}

Answer 2

I have a more kludgy way to solve this problem.

What I do is to set the value of all of the cells in the column to this function: =IF(ISBLANK(A2), “”, “Default Value”)

This makes the cell appear blank until that line is edited. Then the value becomes the default. But then as the user is entering data, they can just overwrite the default value with the correct value without any effort.