google-sheetsgoogle-apps-scripttimestamp

Save timestamp time in a cell and then the timestamp edit time in another cell

I have a Google Form that has students enter information when leaving a class. Column H is one of two items: "Leaving Class" or "Returning to Class". I want to create two columns: Time Out and Time In (by formula or script) that sets the value of the cell based on the value of H. When the student "Leaves Class", I want the timestamp time to be set in Time Out and not change. When the student edits their form and changes it to "Return to Class", I want a Time In column to set the timestamp time.

I tried using a formula in B2 to capture the time:

=if($H2="Leaving Class",time(HOUR(A2),minute(A2),second(A2)),"")

but that changes when the edit causes the timestamp to change. My guess is that I need to use that formula to capture the time, use a script onedit that then sets the value of an adjacent cell (C2) to the value of B2 and then when the timestamp edits, cell D2 will capture the time from the timestamp and cell E2 will be the set value from another script. Then, I could use those values to determine how long a student was gone.

I would hide columns B and D. Here is a picture of the response sheet.

I'm thinking there needs to be some sort of edit variable that is set to "original" on row creation and that sets the C2 time and then changes the variable to "edit" which will edit the E2 time but not change the C2 time.

Spreadsheet of form results: Spreadsheet of form results

This is what I want:

Input Output
Form entry gets created and cell h2="Leaving Class" as student leaves the class to go to the bathroom for instance Cell C2 automatically gets updated with the time from the timestamp and is locked so it won't change (essentially the "value" of the time)
Form gets updated when student returns and h2 becomes "Returning to Class" Cell C2 stays what it was. Cell E2 now becomes the time from the edited timestamp so that C2 is when they left the room and E2 is when they returned.

I've tried a formula but that won't copy the value and lock the cell. I haven't worked in Scripts in a while but understand the basics.

Solution

  • Use onFormSubmit and a Helper Column

    You may try this script that should work with what you'd like to do.

    Code:

    function onFormSubmit(e) {
  var sh = SpreadsheetApp.openById("Your document ID here").getSheetByName("Form Responses 1");
  var data = e.values;
  var dataRow = e.range.getRow();
  var helperColumn = sh.getRange(dataRow, 9).getValue();
  
  if (!helperColumn) {
    if (data[7] == "Leaving Class") {
      sh.getRange(dataRow, 3).setValue(new Date());
      sh.getRange(dataRow, 9).setValue(new Date());
    }
    if (data[7] == "Returning to Class") {
      sh.getRange(dataRow, 5).setValue(new Date());
      sh.getRange(dataRow, 9).setValue(new Date());
    }
  }
  else {
    if (data[7] == "Leaving Class") {
      sh.getRange(dataRow, 5).setValue(helperColumn);
      sh.getRange(dataRow, 3).setValue(new Date());
      sh.getRange(dataRow, 9).setValue(new Date());
    }
    if (data[7] == "Returning to Class") {
      sh.getRange(dataRow, 3).setValue(helperColumn);
      sh.getRange(dataRow, 5).setValue(new Date());
      sh.getRange(dataRow, 9).setValue(new Date());
    }
  }
}

    Sample Output when the "leaving class" option is selected in the form (column H):

    Sample

    Sample2

    Sample Output when a student edits the form and selects the "returning to class" option (column H):

    sample 2

    Sample4

    This uses a helper Column I that mirrors the time values from Column C or Column E. This will enable efficient time tracking.

    For Example, if I select the "Leaving class" option, the corresponding time in the "Time Out" section will automatically populate in Column I. Subsequently, if I modify the form and choose the "Returning to class" option, the data in Column I will be transferred to the "Time In" column.

    Please take note that you should use an installable trigger with these settings.

    Trigger

    References: Class FormTriggerBuilder