Home > Back-end >  How to post current time when checkbox = TRUE
How to post current time when checkbox = TRUE

Time:12-09

I'll try to keep this short and sweet.

I am making a packaging form using Google Sheets. Users fill out the form using the dropdowns on the spreadsheet and press the Submit button to send the data to Packaging Form Responses. This script works perfectly. Now I'm struggling to write a script to make it so that the checkbox in cell O13 auto-populates the data in the "Time Out" section in row 13 with the current time. You'll notice that I have the current time broken down into different cells on row 24 (hh , mm , am/pm). Basically, when the checkbox is = TRUE, I want the data in row 24 to be posted in row 13's "Time Out" section (side note: rows 22-24 will be hidden when the form is live). Haven't used onEdit() function very much so I could use some help here.

Here is my code thus far:

function onEdit(e) {
  var range = e.range;
  var spreadSheet = e.source;
  var column = range.getColumn;
  var row = range.getRow;
  var hours = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Packaging Form').getRange('H24').getValue();
  var minutes = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Packaging Form').getRange('I24').getValue();
  var ampm = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Packaging Form').getRange('J24').getValue();
  
  if(column == 15 && row == 13){  
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Packaging Form').getRange('D13').setValue(hours);
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Packaging Form').getRange('F13').setValue(minutes);
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Packaging Form').getRange('H13').setValue(ampm)    
  } 
}

Not sure what is wrong here, I believe it has to do with my local variables hours, minutes, and ampm.

Also I haven't gotten this far yet, but would like for the current time that gets posted in row 13 to be cleared when the checkbox no longer = TRUE (i.e. when the user un-checks the "Use current time" checkbox, the data in the "Time Out" section is cleared). Any help would be greatly appreciated, thank you.

CodePudding user response:

Replace

  var column = range.getColumn;
  var row = range.getRow;

by

  var column = range.getColumn();
  var row = range.getRow();

or even better

  var column = range.columnStart;
  var row = range.rowStart;

P.S. you might also change SpreadsheetApp.getActiveSpreadsheet() by spreadsheet.

  • Related