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
.