Use checkbox to trigger saving and clearing cells to another google sheet

Klaten Woods 0
Viewed 44

I have a button that activates a macro function that saves data from Sheet1 to database. It works well on a PC. However, to use on iOS it requires a checkbox to trigger macro function. The checkbox is located in (F13).

Below is the code for the 'submitData' function.

function submitData() {
        
  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //active google sheet 
 
  var shSheet1= myGooglSheet.getSheetByName("Sheet1"); //Sheet1 worksheet
 
  var datasheet = myGooglSheet.getSheetByName("Database"); ////Database worksheet
 
  {
  
    var blankRow=datasheet.getLastRow()+1; //next blank row
 
    datasheet.getRange(blankRow, 1).setValue(shSheet1.getRange("B1").getValue()); 
    datasheet.getRange(blankRow, 2).setValue(shSheet1.getRange("D1").getValue()); 
    datasheet.getRange(blankRow, 3).setValue(shSheet1.getRange("F1").getValue()); 
    datasheet.getRange(blankRow, 4).setValue(shSheet1.getRange("B3").getValue()); 
    datasheet.getRange(blankRow, 5).setValue(shSheet1.getRange("B4").getValue()); 
    datasheet.getRange(blankRow, 6).setValue(shSheet1.getRange("B5").getValue());
    datasheet.getRange(blankRow, 7).setValue(shSheet1.getRange("B6").getValue()); 
    datasheet.getRange(blankRow, 8).setValue(shSheet1.getRange("B8").getValue()); 
    datasheet.getRange(blankRow, 9).setValue(shSheet1.getRange("A9").getValue()); 
    datasheet.getRange(blankRow, 10).setValue(shSheet1.getRange("A10").getValue()); 
    datasheet.getRange(blankRow, 11).setValue(shSheet1.getRange("B10").getValue()); 
    datasheet.getRange(blankRow, 12).setValue(shSheet1.getRange("E3").getValue());
    datasheet.getRange(blankRow, 13).setValue(shSheet1.getRange("E4").getValue()); 
    datasheet.getRange(blankRow, 14).setValue(shSheet1.getRange("E5").getValue()); 
    datasheet.getRange(blankRow, 15).setValue(shSheet1.getRange("E6").getValue()); 
    datasheet.getRange(blankRow, 16).setValue(shSheet1.getRange("E7").getValue()); 
    datasheet.getRange(blankRow, 17).setValue(shSheet1.getRange("E8").getValue()); 
    datasheet.getRange(blankRow, 18).setValue(shSheet1.getRange("E9").getValue());
    datasheet.getRange(blankRow, 19).setValue(shSheet1.getRange("E10").getValue()); 
    datasheet.getRange(blankRow, 20).setValue(shSheet1.getRange("E11").getValue()); 
    datasheet.getRange(blankRow, 21).setValue(shSheet1.getRange("E12").getValue()); 
    datasheet.getRange(blankRow, 22).setValue(new Date()).setNumberFormat('yyyy-mm-dd hh:mm:ss');
    
    ;
  
  //Clear data
 
    shSheet1.getRange("B3").clearContent();
    shSheet1.getRange("B4").clearContent();
    shSheet1.getRange("B5").clearContent();
    shSheet1.getRange("B6").clearContent();
    shSheet1.getRange("E3").clearContent();
    shSheet1.getRange("E4").clearContent();
    shSheet1.getRange("E5").clearContent();
    shSheet1.getRange("E6").clearContent();
    shSheet1.getRange("E7").clearContent();
    shSheet1.getRange("E11").clearContent();
    shSheet1.getRange("E12").clearContent();
    shSheet1.getRange("F13").setValue(false);
  }
}

I have found this for the function of triggering a response from check box. It works on computer and IOS. 'checkbox.gs'

function onEdit(e) {
  var checkBoxLocation = "F13";
  var checkBoxCondition = true;
  if (e.range.getA1Notation() == checkBoxLocation) {
    var spreadsheet = SpreadsheetApp.getActive();
    var cell = spreadsheet.getRange('Q1');
    if (e.range.getValue() == checkBoxCondition) {
      cell.setValue("F13 ticked");
    }
    else {
      cell.setValue("F13 unticked");
    }
  }
}

How do I merge the 'checkbox' into the 'submitData' function creating a button function for PC and a checkbox function for iOS?

google-sheets google-apps-script
1 Answers

Try this:

function onEdit(e) {
  //e.source.toast("Entry");
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet0" && e.range.columnStart == 6 && e.range.rowStart == 13 && e.value == "TRUE") {
    //e.source.toast('Flag1');
    const rl1 = sh.getRangeList(["B1", "D1", "F1", "B3", "B4", "B5", "B6", "B8", "A9", "A10", "B10", "E3", "E4", "E5", "E6", "E7", "E8", "E9", "E10", "E11", "E12"]);
    const r = [rl1.getRanges().map(r => r.getValue()).flat()];
    //Logger.log(JSON.stringify(r));
    var dsh = e.source.getSheetByName("Sheet1");
    let lr = dsh.getLastRow();
    //Logger.log(lr);
    dsh.getRange(lr + 1, 1, r.length, r[0].length).setValues(r);
    dsh.getRange(lr + 1, 22).setValue(new Date()).setNumberFormat('yyyy-mm-dd hh:mm:ss');
    dsh.getRangeList(["B3", "B4", "B5", "B6", "E3", "E4", "E5", "E6", "E7", "E11", "E12"]).getRanges().forEach(r => r.clearContent());
    sh.getRange("F13").setValue(false);
  }
}
user30hu0
Related