Skip to content

Instantly share code, notes, and snippets.

@Yagisanatode
Last active November 3, 2021 09:23
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Yagisanatode/77a634bc1b027b258d3885467c393879 to your computer and use it in GitHub Desktop.
Save Yagisanatode/77a634bc1b027b258d3885467c393879 to your computer and use it in GitHub Desktop.
Update Date-Time stamp on checkbox clicked in Google Sheets with Google Apps Script
{
"timeZone": "Australia/Hobart",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"
}
// @author Yagisanatode @link https://www.yagisanatode.com
// Link to attached sheet. Go to File > Make a copy
// @link https://docs.google.com/spreadsheets/d/14orOMBnjpjHewUsWUgunZQKbeNNnZHM9JKMpkaCxRDA/edit#gid=0
// ####### on Edit trigger function #######
/**
* Custom Apps Script Trigger function that runs when sheet is edited.
* @param {Object} e - Apps Script trigger event object.
*/
function onEdit(e) {
dtsOnCheck(e)
}
// ####### Date Time Stamp on Check. #######
/**
* Adds date time stamp on checked. Removes it when unchecked.
* @param {Object} e - Apps Script trigger event object.
* @param {Object.range} Range of edit selection.
* @param {Object.value} value that range is changed to.
*/
function dtsOnCheck(e){
const SS = SpreadsheetApp.getActiveSpreadsheet();
const SheetName = "Sheet1";
const ActiveSheet = SS.getActiveSheet();
const ActiveSheetName = ActiveSheet.getName();
if(ActiveSheetName == SheetName){
const selectionCol = 1;
const actionCol = e.range.columnStart
if(selectionCol === actionCol){
const checkVal = e.value
const actionRow = e.range.rowStart
const inputRange = `B${actionRow}:C${actionRow}`
if(checkVal == 'TRUE'){
console.log("check")
console.log(checkVal)
const date = new Date();
const email = e.user;
ActiveSheet.getRange(inputRange).setValues([[email,date]])
}else if(checkVal == 'FALSE'){
ActiveSheet.getRange(inputRange).clearContent()
};
};
};
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment