Google-sheets – Is it possible to ‘reset’ the value of a cell in one sheet from another sheet

formulasgoogle sheetsimport

I have two separate google sheets (GlobalReport & IndividualReport). Both share information between each-other, but I need to be able to "reset" a cell value in the IndividualReport sheet.

Cell L7 on the IndividualReport sheet is validated (see screenshot) from a range of values form another sheet within the workbook. There are four options to choose from (one is 'no'). I need to be able to "reset" that value to 'No' at random times from the GlobalReport sheet.

I'm currently using =importrange("1Cm0tAGlobalReportSheet", "lists!A1:A30") to populate the validation list.

Is possible to update IndividualReport L7 using some kind of trigger? If yes, any help would be greatly appreciate.

enter image description here

Best Answer

Since the content of L7 is going to be overwritten, spreadsheet formulas cannot perform such reset. You need a script that has the spreadsheet keys of all the spreadsheets to be changed, and has the authority to edit them (i.e., the Google account under which it runs has this authority). It can be either a stand-alone script, or bound to your master spreadsheet, whichever is more convenient. Also, you'll need to know the name of the sheet within the spreadsheet in which the cell L7 is located; apparently it's "FailingStudents". 

function reset() {
  var keys = ['....', '....' ]; // list of spreadsheet ids
  for (var i = 0; i < keys.length; i++) {
    var ss = SpreadsheetApp.openById(keys[i]);
    var sheet = ss.getSheetByName('FailingStudents');
    sheet.getRange('L7').setValue('No');
  }
}

Once you run this, the cell FailingStudents!L7 will be set to No in all listed spreadsheets.

Related Topic