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:
- Did we do work for this client today/yesterday? [YES/NO]
- What did we help this client with today? [textbox][OK/CANCEL] ***skip if previous answer is no
- When should we follow up with this client next? (MM/DD/YY) [textbox][OK/CANCEL]
- Next Follow Up Activity? [textbox][OK/CANCEL]
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.');
}
})
}
CodePudding user response：
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.
CodePudding user response：
SUGGESTION
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:
- The first question should not have a
Textboxbut only give the user to selectYESorNObuttons. - If user selects
NOon the first question, route the user to the third question instead. - 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 rawSheetData = sheet.getDataRange().getDisplayValues().map((x, i) => { return [x, i 1].flat() }).filter(d => d.join('').length != 0);
var arr = rawSheetData.reverse().filter(d => d.filter(e => e).length == 9).reverse();
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"]];
arr.forEach((currentClient, i) => {
if (i != 0) return; //THIS LINE IS JUST FOR TESTING TO ONLY PROCEESS THE FIRST CLIENT
var row = currentClient.pop(); //Get the row number of the current client
var clientDetails = currentClient.join('\n').trim();
/**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.');
}
})
}
Demonstration
- Quick Test
- E.g. If the user selects NO on the first question & then gets skipped to the third question
