Why Are There No Special Components for Checklist?

Feature Requests
1

Please add the ability to add special components (user and time) to the checklist layout. I understand it was designed and intended to update a single value in the row with a true/false value. However it can be used very effectively in cases where teams have open to-do lists or shared inboxes. Having the ability to trace the user that checked the box is critical to the use of the checklist layout in these cases

3 Likes
2

I suppose the checklist layout was only designed and intended to update a single value in the row with a true/false value. I think what you are proposing is a good idea and should be a feature request.

3

Thanks. I’ll add this to the feature request.

1 Like
4

Hi, @Food2Soil

Not sure what your tech expertise is, but if you’re up for it, you could write a script that is triggered by changes to the checkbox and writes the user in a “Checked by” column.

I’ve been playing around with it and this is what I’ve got so far…

// Create a new trigger for onChange
// Don't know if there's a different way, but this way is what i found googling.
function createSpreadsheetChangeTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onChange')
  .forSpreadsheet(ss)
  .onChange().create();
};

function onChange(e) {
  // Make sure to only write change to the required sheet
 // So enter your sheet's name here
  if (e.source.getSheetName() =='Checklist'){
    var d = new Date();
    var now = d.toDateString()+ ' : ' + d.toLocaleTimeString();
    // Write to the relevant column - Y in this example - and row.
    var writeToAddress = 'Y'+e.source.getActiveCell().getRow();
    var cell = e.source.getRange(writeToAddress);
    cell.setValue(e.user+' : '+now);
  }
};

You have had to run this once to load the new trigger and also then have to set the trigger (in the script editor, Edit > Current project’s triggers)

set trigger
set trigger742Ă—712 48.8 KB

Limitations
The biggest one is the column to write to is fixed. So every time you add a new column to your sheet, you will have to manually change the column value. It wouldn’t be overly hard to improve on that.

Also, this is tracking all changes. You probably should add a check so only updates when the column with your checkbox is changed. You could also make it only update when the checkbox is changed to true.

Hope that helps and is not too confusing.

@Jeff_Hager @david @Mark or @George_B may have suggestions for improving the code.

And thanks for asking the question! This code will be very useful to me because I like to track “last edit” using the Current Date/Time component. But it only works on the Add and Edit screens. This code will allow me to edits on any screen - e.g a switch or a checkbox on a detail screen, or a checkbox on a checklist.

2 Likes