Google Script: set cell value + prompt message box

Question

I'm working on the script below (keep in mind that it's far from done):

function Import() {
  var dealCells = getRange('Deals');

  var blank = new Array ("");  

  if (blank.indexOf(dealCells.getValue()) != -1) {
    if (Browser.msgBox('You have not entered all the Deal IDs, do you want to continue anyway?', Browser.Buttons.YES_NO) == 'no') {
      Browser.msgBox('Please fill in all the Deal IDs')

      if (blank.indexOf(dealCells.getValue()) == -1) {
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Worksheet");
        var cell = sheet.getRange(2,12);
        cell.setValue("IMPORT");
      }
    }
  }
}

I'm trying to determine whether or not there is an empty cell in the range A4:A23 in the worksheet 'Newsletter'.

• If there is an empty cell: prompt the yes/no message box

  • If yes: continue with setting the cell value to 'IMPORT' and finish the script without further actions
  • If no: prompt the second message box and finish the script without any further action

Answer 1

Assuming that dealCells is A4:A23:

function Import() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Newsletter');
  // Get range values
  var dealCells = sh.getRange("A4:A23").getValues() 
  // Flatten array for `indexOf()`
  var blank = dealCells.join().split(',');

  if (blank.indexOf("") != -1) { // Check if there is a blank cell
     // If a blank cell exist
    var response = Browser.msgBox('You have not entered all the Deal IDs, do you want to continue anyway?', Browser.Buttons.YES_NO)

    // Handle the msgBox response
    if (response == "yes") { 
      var value = Browser.msgBox('Please fill in all the Deal IDs')
    } else { //response == no
      ...
    }

  } else { // no blank cell
    ...
  }
...
}

edit you can also getRangeByName():

var ss = SpreadsheetApp.getActiveSpreadsheet(); var dealCells = ss.getRangeByName('Deals').getValues();