4

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

  • You need a script. There are various triggers but it's unclear when exactly you want the change to happen (what does "at random times" mean?)\ – user79865 Jan 22 '16 at 23:16
  • @Norma This will be used at a high school. We need to reset the value to "No" once a semester. – Mr. B Jan 23 '16 at 0:43
  • Okay. Apparently, L7 is something where the end user of your individual reports will enter things? If so, the reset cannot be done with a formula (since L7 will be overwritten), and requires a script that is run by you (maybe from the GlobalReport). But then you'd need to be able access all the copies of the Individual Report out there -- having permissions and spreadsheet keys for all of them. Is this feasible? – user79865 Jan 23 '16 at 0:56
  • @Norma Yes. I have editing rights to all of the individual reports and they've all been set up so info can be shared between the two sheets. I also 'own' all the sheets. L7 will be changed every semester by a teacher. I need to be able to 'reset' it back to the "no" option at the start of every semester. Thanks! – Mr. B Jan 23 '16 at 3:07
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.