How Can I Find a Button's Location Within a Spreadsheet With Google Apps Script?

Question

I am creating a classroom attendance log in Google Sheets. I have a column with each student's name and a column for the time when they arrived in class. I want to include a button for every student that I can click to run a script that inserts the current time into the arrival time cell for that student. Is there a way to find the location of a button so that my script can insert the time into the appropriate cell?

Here's how I have my sheet set up: https://docs.google.com/spreadsheets/d/19wRj1zPGf1hm8PDUYtEF2XUxirhk2lIJbHAZF9e9YqE

Is there a better way to do this? I played around with the NOW() formula, but it updates every time I do anything else in the sheet. This might be a viable option if I could somehow force it to stay the same.

Answer 1

Based on my understanding, you want to get the cell row of the drawing that is being clicked to include the time entry for a particular student.

That is not possible, when you click a drawing/image, you are only clicking the drawing/image. It doesn't make the cell where the image located be active. Therefore, we cannot use the getActiveRange and getSelection method to locate the cell where the image is located.

One workaround that I could think of is to use checkboxes instead of drawings. When checkboxes are clicked, it makes the cell active.

Sample Code:

function onEdit(e) {
  
  const sheet = e.source.getActiveSheet();
  const cell = e.range
  
  // Check if checkbox was checked and there is no time-entry yet
  if (cell.isChecked() == true && cell.offset(0,-1).isBlank()){
    
    //Include time entry on the left side of the checkbox
    //var date = Utilities.formatDate(new Date(), 'America/Los_Angeles', 'MMMM dd, yyyy HH:mm:ss Z');
    var date = Utilities.formatDate(new Date(), 'America/Los_Angeles', 'HH:mm:ss Z');
    cell.offset(0,-1).setValue(date);
    
    // Uncheck the checkbox
    cell.setValue(false);
  } else if (cell.isChecked() == true) {
    // Uncheck the checkbox
    cell.setValue(false);
  }
}

You can change the format of your timestamp depending on your preference. In this example I only displayed the current time. See Working with Dates and Times

Output:

I reset the checkbox after setting the timestamp.