CODEWITHSUNDEEP
loopsuser-interfacegoogle-apps-scriptgoogle-sheetsprompt
Ricardo Barrios
Ricardo Barrios

Reputation: 29

How to create a popup using Google Apps Script only if a certain button is pushed on the previous popup and remove text box?

My code below opens 4 text boxes, 1 asking a YES/NO question and 3 asking OK/CANCEL questions. Every one of them accepts text, but I want the first question to only accept buttons YES and NO without a text box. Also, if the answer to the first question is NO, I want it to skip the second question and go straight to the third question.

Questions are as follows:

function TextBox() {
  var ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.openById('1JZ-v5n5m0_lyaoLgsHDAa78AtYNP5AsUDo2NRpJbwG4').getSheetByName('HOT & WARM CLIENTS');
  sheet.sort(9)

  var today = Utilities.formatDate(new Date(), "GMT-6", "MM/dd/yy")
  var ui = SpreadsheetApp.getUi();
  var valuesToCopy = sheet.getRange("C5:J5").getDisplayValues().map(r => r.join(' \n '));

var ar2 = [
{
  message: valuesToCopy + "\n\n Did we do work for this client today/yesterday? (YES/NO)",
  range: "G5"
}

];

var ar3 = [
    
    { // promptUPDATE3
      message: valuesToCopy + "\n\n What did we help this client with today?",
      range: "H5"
    },
    
  ];

var ar = [
    // { // promptUPDATE3
    //   message: valuesToCopy + "\n\n What did we help this client with today?",
    //   range: "PREVIOUS"
    // },
    { // promptUPDATE
      message: valuesToCopy + "\n\n When should we follow up with this client next? (MM/DD/YY)",
      range: "I5"
    },
    { // promptUPDATE2
      message: valuesToCopy + "\n\n Next Follow Up Activity?",
      range: "J5"
    }
  ];

ar2.forEach(({message, range }) => {
  var res = ui.prompt(message, ui.ButtonSet.YES_NO);
  if (res.getSelectedButton() == ui.Button.YES) {
    sheet.getRange("G5").setValue(today);

  } else { 
    Logger.log('The user clicked "No" or the dialog\'s close button.');
  }
})


  ar3.forEach(({ message, range }) => {
    var res = ui.prompt(message, ui.ButtonSet.OK_CANCEL);
    var lastRowSourceData = sheet.getRange("H5").getValue();
    var lastActivity = SpreadsheetApp.openById('1JZ-v5n5m0_lyaoLgsHDAa78AtYNP5AsUDo2NRpJbwG4').getSheetByName('HOT & WARM CLIENTS').getRange("H5").getValue();
    if (res.getSelectedButton() == ui.Button.OK) {
      sheet.getRange(range).setValue(lastActivity+" | "+res.getResponseText());
    } else {
      Logger.log('The user clicked "No" or the dialog\'s close button.');
    }
  })



  ar.forEach(({ message, range }) => {
    var res = ui.prompt(message, ui.ButtonSet.OK_CANCEL);
    if (res.getSelectedButton() == ui.Button.OK) {
      sheet.getRange(range).setValue(res.getResponseText());

    } else {
      Logger.log('The user clicked "No" or the dialog\'s close button.');
    }
  })
  
  }

Upvotes: 0

Views: 858

Answers (2)

SputnikDrunk2
SputnikDrunk2

Reputation: 4048

SUGGESTION [SCRIPT UPDATED]

Perhaps you can try this tweaked script below. This will use the alert Class Ui method for the first question & an if condition to skip the order of questions if the user selects NO.

In my understanding, here is the flow you want to achieve:

  1. The first question should not have a Textbox but only give the user to select YES or NO buttons.
  2. If user selects NO on the first question, route the user to the third question instead.
  3. Otherwise, the user will be prompted with the questions in order.

NOTE: This sample script will only process the first client on your sheet just like on your actual script. If I have misunderstood something Or if there's anything else missing, feel free to let me know.

Sample Tweaked Script

function TextBox() {
  var ui = SpreadsheetApp.getUi();
  var today = Utilities.formatDate(new Date(), "GMT-6", "MM/dd/yy");
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('HOT & WARM CLIENTS');
  sheet.sort(9);
  var sheetData = sheet.getRange("C5:J5").getDisplayValues();
  var questions = [['\n\n Did we do work for this client today/yesterday? (YES/NO)', "G"],
  ['\n\n What did we help this client with today?', "H"],
  ['\n\n When should we follow up with this client next? (MM/DD/YY)', "I"],
  ['\n\n Next Follow Up Activity?', "J"]];


  var row = "5"; //ONLY process current client on 5th row
  var clientDetails = sheetData[0].join('\n');

  /**First question */
  var q1 = ui.alert(clientDetails + questions[0][0], ui.ButtonSet.YES_NO)
  if (q1 == ui.Button.YES) {
    sheet.getRange(questions[0][1] + row).setValue(today);
    /**End of the First question */

    /**Second question */
    var q2 = ui.prompt(clientDetails + questions[1][0], ui.ButtonSet.OK_CANCEL);
    var lastActivity = sheet.getRange(questions[1][1] + row).getValue();
    q2.getSelectedButton() == ui.Button.OK ? sheet.getRange(questions[1][1] + row).setValue(lastActivity + " | " + q2.getResponseText()) : console.log('The user clicked "No" or the dialog\'s close button.');
    /**End of the Second question*/

    /**Third question */
    var q3 = ui.prompt(clientDetails + questions[2][0], ui.ButtonSet.OK_CANCEL);
    q3.getSelectedButton() == ui.Button.OK ? sheet.getRange(questions[2][1] + row).setValue(q3.getResponseText()) : console.log('The user clicked "No" or the dialog\'s close button.');
    /**End of the Third question*/

    /**Fourth question */
    var q4 = ui.prompt(clientDetails + questions[3][0], ui.ButtonSet.OK_CANCEL);
    q4.getSelectedButton() == ui.Button.OK ? sheet.getRange(questions[3][1] + row).setValue(q4.getResponseText()) : console.log('The user clicked "No" or the dialog\'s close button.');
    /**End of the Fourth question*/
  } else {
    /**Skip to the third question if 'NO' was selected on the first question*/
    var q3 = ui.prompt(clientDetails + questions[2][0], ui.ButtonSet.OK_CANCEL);
    q3.getSelectedButton() == ui.Button.OK ? sheet.getRange(questions[2][1] + row).setValue(q3.getResponseText()) : console.log('The user clicked "No" or the dialog\'s close button.');

    /**End to fourth question*/
    var q4 = ui.prompt(clientDetails + questions[3][0], ui.ButtonSet.OK_CANCEL);
    q4.getSelectedButton() == ui.Button.OK ? sheet.getRange(questions[3][1] + row).setValue(q4.getResponseText()) : console.log('The user clicked "No" or the dialog\'s close button.');

  }
}

Demonstration

- Quick Test

enter image description here

- E.g. If the user selects NO on the first question & then gets skipped to the third question

enter image description here

References

Upvotes: 1

Cooper
Cooper

Reputation: 64100

I don't think putting comments inside you objects is a good idea

Try it this way:

function lfunko() {
  var ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.openById('1JZ-v5n5m0_lyaoLgsHDAa78AtYNP5AsUDo2NRpJbwG4').getSheetByName('HOT & WARM CLIENTS');
  sheet.sort(9)
  var today = Utilities.formatDate(new Date(), "GMT-6", "MM/dd/yy")
  var ui = SpreadsheetApp.getUi();
  var valuesToCopy = sheet.getRange("C5:J5").getDisplayValues().flat().map(r => r.join(' \n '));
  var ar2 = [{ message: valuesToCopy + "\n\n Did we do work for this client today/yesterday? (YES/NO)", range: "G5" }];
  var ar3 = [{ message: valuesToCopy + "\n\n What did we help this client with today?", range: "H5" }];
  var ar = [{ message: valuesToCopy + "\n\n What did we help this client with today?", range: "PREVIOUS" }, { message: valuesToCopy + "\n\n When should we follow up with this client next? (MM/DD/YY)", range: "I5" }, { message: valuesToCopy + "\n\n Next Follow Up Activity?", range: "J5" }];
  ar2.forEach(obj => {
    var res = ui.prompt(obj.message, ui.ButtonSet.YES_NO);
    if (res.getSelectedButton() == ui.Button.YES) {
      sheet.getRange("G5").setValue(today);

    } else {
      Logger.log('The user clicked "No" or the dialog\'s close button.');
    }
  });
  ar3.forEach(obj => {
    var res = ui.prompt(obj.message, ui.ButtonSet.OK_CANCEL);
    var lastRowSourceData = sheet.getRange("H5").getValue();
    var lastActivity = SpreadsheetApp.openById('1JZ-v5n5m0_lyaoLgsHDAa78AtYNP5AsUDo2NRpJbwG4').getSheetByName('HOT & WARM CLIENTS').getRange("H5").getValue();
    if (res.getSelectedButton() == ui.Button.OK) {
      sheet.getRange(range).setValue(lastActivity + " | " + res.getResponseText());
    } else {
      Logger.log('The user clicked "No" or the dialog\'s close button.');
    }
  });
  ar.forEach((obj) => {
    var res = ui.prompt(obj.message, ui.ButtonSet.OK_CANCEL);
    if (res.getSelectedButton() == ui.Button.OK) {
      sheet.getRange(obj.range).setValue(res.getResponseText());

    } else {
      Logger.log('The user clicked "No" or the dialog\'s close button.');
    }
  });
}

And the UI does not contain any drop down selections. You would have to build one with html.

Upvotes: 0

Related Questions