Created
July 14, 2020 22:44
-
-
Save simonho288/8bfccc5f44aa58e96c6fa4896e3119da to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| const gSheetUrl = 'Copy & paste your Google Sheet URL here'; | |
| const gFormUrl = 'Copy & paste your Google Form URL here'; | |
| function updateFormBySheetData() { | |
| // Open the Google Sheet | |
| let gSheet = SpreadsheetApp.openByUrl(gSheetUrl).getSheetByName('Products'); | |
| // Get the product cells data | |
| let products = gSheet.getRange(2, 1, gSheet.getLastRow() - 1, 2).getValues(); | |
| // Remap the matrix into two arrays | |
| let productNames = products.map(p => p[0]); | |
| let productPrices = products.map(p => p[1]); | |
| // Update the form fields by the array values | |
| assignFormFields(productNames, productPrices); | |
| } | |
| function assignFormFields(productNames, productPrices) { | |
| // Open the Google Form | |
| let gForm = FormApp.openByUrl(gFormUrl); | |
| // Get all the form fields | |
| let formItems = gForm.getItems(); | |
| // Find the product field & price field | |
| let productField = formItems.find(item => item.getTitle() == 'Product').asListItem(); | |
| let priceField = formItems.find(item => item.getTitle() == 'Unit Price').asTextItem(); | |
| // Assign the product name dropdown options | |
| productField.setChoiceValues(productNames); | |
| // Restrict the price must be one of product price | |
| let pattern = productPrices.join('|'); | |
| let patternHelp = productPrices.join(' or '); | |
| var priceValidation = FormApp.createTextValidation().requireTextMatchesPattern(pattern).setHelpText('Price must be ' + patternHelp).build(); | |
| priceField.setValidation(priceValidation); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment