I have a Google Sheet set up with two buttons on the Form sheet, which are attached to two different scripts. They work perfectly on PC, but unfortunately, custom buttons still do not appear to work on the Google Sheets app for tablets. I was able to incorporate a workaround via a dropdown box, but that is still a bit finicky, so I'm wondering whether I could just switch both PC and tablet users to checkboxes instead.
If the checkbox in cell G3 is checked, the AUTOFILL script should run and the checkbox should be cleared; subsequently, if the checkbox in cell G5 is checked, the UPDATE script should run and its checkbox be cleared.
What would be the best way of doing this, now that checkboxes are a thing in Google Sheets?
Here is the code I am currently using, working for both the buttons and the dropdown:
function onEdit(e) {
if (e.range.getA1Notation() == 'D3') {
if (/^\w+$/.test(e.value)) {
eval(e.value)();
e.range.clearContent();
}
}
}
function AUTOFILL() {
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form');
var valueOfData = sheet1.getRange(sheet1.getLastRow(), 1).getValue();
sheet2.getRange('B3').setValue(valueOfData + 1);
}
function UPDATE() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Form");
var dataSheet = ss.getSheetByName("Data");
var values = formSS.getRange("B3:B6").getValues().reduce(function(a, b) {
return a.concat(b)
});
var partNum = values[0];
var row;
dataSheet.getDataRange().getValues().forEach(function(r, i) {
if (r[0] === partNum) {
row = i + 1
}
})
row = row ? row : dataSheet.getLastRow() + 1;
var data = dataSheet.getRange(row, 1, 1, 4).getValues()[0].map(function (el, ind){
return el = values[ind] ? values[ind] : el;
})
var now = [new Date()];
var newData = data.concat(now)
dataSheet.getRange(row, 1, 1, 5).setValues([newData]);
formSS.getRange("B3:B6").clearContent()
}