CODEWITHSUNDEEP
htmlgoogle-apps-scriptgoogle-sheets
arul selvan
arul selvan

Reputation: 624

Keep HTML window open till user closes it

I get a mobile number from the user. I will check and capture existing records in a HTML. I want to display this to the user and then I want to ask the user to enter choice whether to add a new record or not.

The below code is working. Only problem is the HTML window is closing on its own. The user is not able to view it.

I want the HTML must be closed manually before the next step.

Any help will be appreciated.

I am open to any other method.

function book_new_call() {
  var ss=SpreadsheetApp.getActive();
  var today=new Date();
  var newcust=true;
  var cusmob =  SpreadsheetApp.getUi().prompt("Enter Customer Mobile No").getResponseText();
  var cussht = ss.getSheetByName("Cust_Mast");
  var cuslr=cussht.getLastRow()
  var titles = cussht.getRange("A1:G1").getValues();
  var vA = cussht.getRange("A:G").getValues();
  var html="Cust Master<style>th,td{border:1px solid black;}</style><table>";
  for(var i=1;i< cuslr ;i++) {
    if (vA[i][0]==cusmob  || vA[i][4]==cusmob  ) {
      newcust=false;
      if (vA[i][4]==cusmob){vA[i][0]=cusmob; cusmob = vA[i][0] };
      for(var j=0;j<=6;j++) {
        if ( vA[i][j] =="") {} else {
          if (j==5){
            vA[i][j]=Utilities.formatDate(vA[i][j],"GMT+05:30", "dd-MM-yy");
          }//if date
          html+='<tr>';
          html+=Utilities.formatString('<th>%s</th>',titles[0][j]);
          html+=Utilities.formatString('<td>%s</td>',vA[i][j]);
          html+='</tr>'};//if
      }//for j
    }//if item match
  }//for i
  var userInterface=HtmlService.createHtmlOutput(html);
  //SpreadsheetApp.getUi().showModelessDialog(userInterface, cusmob );
  SpreadsheetApp.getUi().showModalDialog(userInterface, cusmob );
  if (newcust) {
    var choice =  SpreadsheetApp.getUi().prompt("1 to add customer and Call, 2 to add customer only").getResponseText();
    if (choice=="1") {
      cussht.appendRow([cusmob]); 
      var nxcall=1+calsht.getRange("A"+callr).getValue();
      calsht.appendRow([ nxcall,today,"",cusmob]);
    } 
    if (choice=="2") {
      cussht.appendRow([cusmob]); 
    }
  } else {
    var choice =  SpreadsheetApp.getUi().prompt("1 to add Call").getResponseText();
    //var response = Browser.msgBox('Do you want to add new customer and register new call', 'Press Yes or Cancel', Browser.Buttons.YES_NO);
    if (choice=="1") {
      var nxcall=1+calsht.getRange("A"+callr).getValue();
      calsht.appendRow([ nxcall,today,"",cusmob]);
      calsht.activate();
    } else {
      if (pcalls>0) {calsht.getRange("A"+pcall).activate()} else {
          if (ccalls>0) { calsht.getRange("A"+ccall).activate() } }
    }
  }
}

Upvotes: 1

Views: 47

Answers (1)

Tanaike
Tanaike

Reputation: 201553

I believe your goal as follows.

  • You want to keep the dialog of SpreadsheetApp.getUi().showModalDialog(userInterface, cusmob ), until the user close the dialog.

Modification points:

  • When SpreadsheetApp.getUi().showModalDialog(userInterface, cusmob ) is run, the process of this dialog is released from the process of the running function. By this, the next script of the function is run. So SpreadsheetApp.getUi().prompt() overwrites the existing dialog. By this, SpreadsheetApp.getUi().showModalDialog(userInterface, cusmob ) is removed soon. I think that this is the reason of your issue.

In order to achieve your goal, I would like to propose the following 2 patterns.

Pattern 1:

In this pattern, the sidebar is used instead of the dialog. In this case, please modify your script as follows.

From:

SpreadsheetApp.getUi().showModalDialog(userInterface, cusmob );

To:

SpreadsheetApp.getUi().showSidebar(userInterface.setTitle(cusmob));

Pattern 2:

When you are required to use the dialog, in this pattern, the dialog of SpreadsheetApp.getUi().showModalDialog(userInterface, cusmob ); is used. Please modify your script as follows.

In this pattern, after SpreadsheetApp.getUi().showModalDialog(userInterface.evaluate(), cusmob) was run, next() function is run with google.script.run by clicking "OK" button. But in this case, it is required to send the parameters for using the function next(). From your script, I sent the variables of [newcust, cussht.getSheetName(), calsht.getSheetName(), cusmob, callr, pcalls, pcall, today.toISOString()]. And, the sheet name and date string are converted to the sheet object and the date object in next(), respectively.

From:

  var userInterface=HtmlService.createHtmlOutput(html);
  //SpreadsheetApp.getUi().showModelessDialog(userInterface, cusmob );
  SpreadsheetApp.getUi().showModalDialog(userInterface, cusmob );
  if (newcust) {
    var choice =  SpreadsheetApp.getUi().prompt("1 to add customer and Call, 2 to add customer only").getResponseText();
    if (choice=="1") {
      cussht.appendRow([cusmob]); 
      var nxcall=1+calsht.getRange("A"+callr).getValue();
      calsht.appendRow([ nxcall,today,"",cusmob]);
    } 
    if (choice=="2") {
      cussht.appendRow([cusmob]); 
    }
  } else {
    var choice =  SpreadsheetApp.getUi().prompt("1 to add Call").getResponseText();
    //var response = Browser.msgBox('Do you want to add new customer and register new call', 'Press Yes or Cancel', Browser.Buttons.YES_NO);
    if (choice=="1") {
      var nxcall=1+calsht.getRange("A"+callr).getValue();
      calsht.appendRow([ nxcall,today,"",cusmob]);
      calsht.activate();
    } else {
      if (pcalls>0) {calsht.getRange("A"+pcall).activate()} else {
          if (ccalls>0) { calsht.getRange("A"+ccall).activate() } }
    }
  }
}

To:

  html += '<input type="button" value="ok" onclick="next()"><script>function next() {google.script.run.next(JSON.parse(<?= JSON.stringify(values) ?>))}</script>';
  var userInterface = HtmlService.createTemplate(html);
  userInterface.values = [newcust, cussht.getSheetName(), calsht.getSheetName(), cusmob, callr, pcalls, pcall, today.toISOString()];
  SpreadsheetApp.getUi().showModalDialog(userInterface.evaluate(), cusmob);
}

function next([newcust, cussht, calsht, cusmob, callr, pcalls, pcall, today]) {
  var ss = SpreadsheetApp.getActive();
  cussht = ss.getSheetByName(cussht);
  calsht = ss.getSheetByName(calsht);
  today = new Date(today);

  if (newcust) {
    var choice =  SpreadsheetApp.getUi().prompt("1 to add customer and Call, 2 to add customer only").getResponseText();
    if (choice=="1") {
      cussht.appendRow([cusmob]); 
      var nxcall=1+calsht.getRange("A"+callr).getValue();
      calsht.appendRow([ nxcall,today,"",cusmob]);
    } 
    if (choice=="2") {
      cussht.appendRow([cusmob]); 
    }
  } else {
    var choice =  SpreadsheetApp.getUi().prompt("1 to add Call").getResponseText();
    //var response = Browser.msgBox('Do you want to add new customer and register new call', 'Press Yes or Cancel', Browser.Buttons.YES_NO);
    if (choice=="1") {
      var nxcall=1+calsht.getRange("A"+callr).getValue();
      calsht.appendRow([ nxcall,today,"",cusmob]);
      calsht.activate();
    } else {
      if (pcalls>0) {calsht.getRange("A"+pcall).activate()} else {
          if (ccalls>0) { calsht.getRange("A"+ccall).activate() } }
    }
  }
}

Note:

  • In your script, there are several variables which are not declared in the function. So please be careful this. And, if you want to use more variables, please modify the script.
  • Above scripts are the simple modifications. So please modify them for your actual situation.

References:

Upvotes: 2

Related Questions