Add Validations to Prompts in Google Sheets, Docs, Slides, and Forms: LC021
Introduction
In Google Sheets, Docs, Slides, and Forms, you can easily use the prompt method from the Class Ui to collect user inputs in your Google Apps Script project. However, it’s not easy to validate the content users input. In this live coding Google Apps Script, I am sharing this utility function that you can easily add your validation rules to a prompt until the user enters the valid content or cancels the prompt.
YouTube
Check this video if you prefer to watch the video instructions. (This is a live coding project, so you can follow me step by step)
Instructions (3 Steps)
Follow the steps below to set up the project on your Google Drive.
Step 1
Make a copy of my project with the link below, the script will be copied to yours.
Step 2
Complete the authorization requirement by clicking any button from the custom menu “LC021”. Follow the guide below if you are not familiar with this process.
A Complete Guide to Authorize GAS Project
Step 3
After the authorization, you can try the demo functions I created in the project. For example, with the “Get name” function.
* After you enter the name, the script will check if the name you entered was already entered in column A of Sheet1.
* Your entry will be appended to column A if it’s a valid name input.
Utility Functions
createValidator_ can create a validator function with a list of rules for validating a value.
/**
* @param {Function[]} rules - A list of functions which return true or an error message
* @param {any} value - The value to be checked
* @returns {string[]} A list of error messages
*/
const createValidator_ = (rules) => (value) =>
rules
.map((rule) => rule(value))
.filter((v) => v !== true)
.map((v) => `${v}\nYour input: ${value}`);
createAlert_ is a helper function for creating alerts.
* This is for Google Sheets, you need to update the Ui object for others. E.g. for Google Docs: the Ui will be DocumentApp.getUi().
const createAlert_ =
(buttons = SpreadsheetApp.getUi().ButtonSet.OK) => (title) => (msg) =>
SpreadsheetApp.getUi().alert(title, msg, buttons);
Core Functions
Create a prompt function with a list of rules to ensure the end user’s input meets our requirements.
/**
* @param {string} title - The title of the prompt
* @param {string} msg - The message body of the prompt
* @param {Function[]|undefined} rules - A list of validator functions
* @returns {string|null} return null if canceled else return the value entered
*/
const createInput_ = (title) => (msg) => (rules) => {
const ui = SpreadsheetApp.getUi();
const input = ui.prompt(title, msg, ui.ButtonSet.OK_CANCEL);
if (input.getSelectedButton() !== ui.Button.OK) return null;
const value = input.getResponseText();
if (!rules) return value;
const validator = createValidator_(rules);
const firstErrorMessage = validator(value)[0];
if (!firstErrorMessage) return value;
return createInput_(title)(firstErrorMessage)(rules);
};
Demo Rules
A rule is basically a function that takes the value to be checked and returns true or an error message when the check fails.
1st rule - name length should be greater or equal to 3
2nd rule - name length should be less or equal to 10
3rd rule - name should only contains letters
4th rule - name should not be used in column A of the active sheet
const nameRules = [
(name) => name.length >= 3 || "❗️ Name should have 3 letters at least.",
(name) => name.length <= 10 || "❗️ Name should have 10 letters at most.",
(name) => /^[a-zA-Z]+$/.test(name) || "❗️ Only letters are allowed.",
(name) =>
/^[A-Z][a-z]+$/.test(name) ||
"❗️Only and the first letter must be upper case.",
nameCheck,
];
The last rule to check if the name is already used in the active sheet at column A.
const nameCheck = (name) => {
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheets().find((v) => v.getSheetId() == 0);
const values = sheet.getDataRange().getValues();
const foundName = values.find((v) => v[0] == name);
if (!foundName) return true;
return `❗️ "${name}" was already used, try another one please.`;
};
Demo Use Case
Create the name input function with title and message for end users.
const nameInput_ = createInput_("👉 Name")("Enter your name here:");
Here is the script function to collect the name from the end user and update the name list when the input is valid.
const getName = () => {
const name = nameInput_(nameRules);
if (name === null) {
return warning_("Action was canceled.");
}
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheets().find((v) => v.getSheetId() == 0);
sheet && sheet.appendRow([name]);
success_(`The name entered was ${name}.`);
};
LiveCoding (Follow this Playlist for New Projects)
Links
Hire me on Upwork (work with me)
Comments
Post a Comment