How to assign a function to a checkbox or value instead of button (drawing)?

Question

Since buttons only work on PC I am trying to make this sheet work properly on android. I'll be attaching this time keeping sheet link below.

https://docs.google.com/spreadsheets/d/1v8y91EIVoSWexMIMjghB2wEbO3pvUltjUSbTtogvbIU/edit?usp=sharing

Here is the script as well:

function clockIn() {

//DEFINE ALL ACTIVE SHEETS
var ss = SpreadsheetApp.getActiveSpreadsheet();

//DEFINE MAIN SHEET
var mainSheet = ss.getSheetByName("MAIN");

//LAST ROW ON MAIN SHEET
var lastRow = mainSheet.getLastRow();

for (var j = 5; j <= lastRow; j++)
{
// CHECK CLOCK IN
if(mainSheet.getRange('B1:B1').getValue() ==  mainSheet.getRange(j, 1).getValue() && mainSheet.getRange(j,3).getValue() == '')
{
Browser.msgBox('Need to Clock Out before Clocking IN');
return;
}

}

// ADD CLOCK IN RECORD
mainSheet.getRange(lastRow+1,1).setValue(mainSheet.getRange('B1:B1').getValue()).setFontSize(12);
mainSheet.getRange(lastRow+1,2).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm:ss A/P").setHorizontalAlignment("left").setFontSize(12);

}


function clockOut() {

//DEFINE ALL ACTIVE SHEETS
var ss = SpreadsheetApp.getActiveSpreadsheet();

//DEFINE MAIN SHEET
var mainSheet = ss.getSheetByName("MAIN");

//LAST ROW ON MAIN SHEET
var lastRow = mainSheet.getLastRow();

var foundRecord = false;

for (var j = 5; j <= lastRow; j++)
{
// FIND CLOCK IN RECORD
if(mainSheet.getRange('B1:B1').getValue() ==  mainSheet.getRange(j, 1).getValue() && mainSheet.getRange(j,3).getValue() == '')
{
// UPDATE CLOCK IN RECORD
mainSheet.getRange(j,3).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm:ss A/P").setHorizontalAlignment("left").setFontSize(12);
var totalTime = (mainSheet.getRange(j,3).getValue() - mainSheet.getRange(j,2).getValue()) /(60*60*1000);
mainSheet.getRange(j,4).setValue(totalTime.toFixed(2)).setNumberFormat("#0.00").setHorizontalAlignment("left").setFontSize(12);
foundRecord = true;
}

}

// IF NO CLOCK IN RECORD
if(foundRecord == false)
{
Browser.msgBox('Need to Clock In First');
return;
}

// CALL TOTAL HOURS
TotalHours();

}

function TotalHours()
{

//DEFINE ALL ACTIVE SHEETS
var ss = SpreadsheetApp.getActiveSpreadsheet();

//DEFINE MAIN SHEET
var mainSheet = ss.getSheetByName("MAIN");

//LAST ROW ON MAIN SHEET
var lastRow = mainSheet.getLastRow();

//DEFINE ARRAY
var totals = [];

//LOOP THROUGH ALL RATES
for (var j = 5; j <= lastRow; j++)
{
var rate = mainSheet.getRange(j, 4).getValue();
var name = mainSheet.getRange(j, 1).getValue();
var foundRecord = false;

for(var i = 0; i < totals.length; i++)
{
//FOUND RECORD ADD TO TOTAL
if(name == totals[i][0] && rate != '')
{
totals[i][1] =  totals[i][1] + rate;
foundRecord = true;
}
}

//ADD NEW RECORD, EXISTING RECORD NOT FOUND
if(foundRecord == false && rate != '')
{
totals.push([name, rate]);
}

}

//CLEAR DATA
mainSheet.getRange("F5:G1000").clear();

//DISPLAY TOTALS
for(var i = 0; i < totals.length; i++)
{
mainSheet.getRange(5+i,6).setValue(totals[i][0]).setFontSize(12);
mainSheet.getRange(5+i,7).setValue(totals[i][1]).setFontSize(12);
}
}

Answer 1

I believe your goal as follows.

• You want to run the functions of clockIn() and clockOut() by checking the checkboxes instead of the buttons.
• You want to achieve this using Google Apps Script.

Modification point:

• In this case, in order to run the script by checking the checkbox, it uses the OnEdit trigger. From your current script, I thought that the simple trigger of OnEdit can be used.

When this point is reflected to your script, it becomes as follows.

Sample script:

At first, please insert 2 checkboxes to the cells "F1" and "F2. In this sample script, when the checkbox of "F1" is checked, the function clockIn() is run. When the checkbox of "F2" is checked, the function clockOut() is run. And, please copy and paste the following script to the script editor. In order to run the script, please check the checkbox.

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const checkboxes = {"F1": clockIn, "F2": clockOut};
  const a1Notation = range.getA1Notation();
  if (sheet.getSheetName() != "MAIN" || !checkboxes[a1Notation] || !range.isChecked()) return;
  checkboxes[a1Notation]();
  range.uncheck();
}

Note:

• If you want to use the methods which are required to authorize, please use the installable OnEdit trigger instead of the simple trigger. Please be careful this.
• If in your situation, when several users are used the checkboxes, I would like to recommend to use the lockService.

References:

• Simple Triggers
• Installable Triggers
• Lock Service

Answer 2

function onEdit(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=='your sheet name' && e.range.getA1Notation()=='check box location ' {
    run function 
  }
}