CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jon Bowles
Jon Bowles

Reputation: 129

Problem with the order of IF statements Google Script

I am struggling to set up my IF statements properly, basically I want to check if there is data in column J, if empty continue on with the rest of the script, if there is data ask if the user wants to continue, and then either continue or terminate depending on their response.

Here is the start of my code:

function exportSg() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Subgrade'); //source sheet
  var testrange = sheet.getRange('J18:J'); //range to check
  var testvalue = (testrange.getValues());
  var ui = SpreadsheetApp.getUi();
  
  for (i=0; i<testvalue.length;i++) {
  if ( testvalue[i] != "") {
  
  var response = ui.alert("Gaps greater than 50 meters identified in report, unhide column J to identify. \n\n Do you want to cancel the export?",ui.ButtonSet.YES_NO);
  Logger.log(response);
  
  if (response == ui.Button.YES) {
  SpreadsheetApp.getActive().toast("Export Cancelled...")}else {

SpreadsheetApp.getActive().toast("Exporting Subgrade Report...");

The problem seems to be when there is no data in column J it does not continue with the export.

Anyone fancy shedding some light??

Upvotes: 0

Views: 42

Answers (1)

mshcruz
mshcruz

Reputation: 1987

As mentioned in a previous answer to you, you can use a break to stop a loop.

In your case, that means to have a break when the user clicks the YES button:

if (response == ui.Button.YES) {
  SpreadsheetApp.getActive().toast("Export Cancelled...");
  break;
}

If you want to export even if there is no data to be used by the for statement, you need to move the export part to outside of it:

function exportSg() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Subgrade'); //source sheet
  var testrange = sheet.getRange('J18:J'); //range to check
  var testvalue = (testrange.getValues());
  var ui = SpreadsheetApp.getUi();

  var shouldExport = true;
  for (i=0; i<testvalue.length;i++) {
    if ( testvalue[i] != "") {
      var response = ui.alert("Gaps greater than 50 meters identified in report, unhide column J to identify. \n\n Do you want to cancel the export?",ui.ButtonSet.YES_NO);
      Logger.log(response);
  
      if (response == ui.Button.YES) {
        SpreadsheetApp.getActive().toast("Export Cancelled...");
        shouldExport = false;
        break;
      }
    }
  }

  if (shouldExport) {
    myExportFunction();
  }

}

Upvotes: 1

Related Questions