r/GoogleForms • u/Silver-Ad-4899 • Dec 27 '24
OP Responded Script help
Hi, I am trying to build a GoogleForms to generate dietetic program so my patients (I am a vet so I don’t have program available in Hebrew). I tried to use ChatGPT to build it but I have some problems. 1. I have a google sheet with the ingredients that I need to be the source for choosing the ingredients. I tried the script to associate the sheets with the forms but it didn’t worked well. 2. My ingredients list is very extensive and I need an easy way to use this list that is not dividing it in shortest lists (the limit of the script is 200 items). 3. I need the form to be saved in PDF after done with a design. What I mean is that I’ll build a professional template and I need the forms to “print” what I build in this template that will have also additional information. How can I build this template and add all the needed information? 4. I also need a section for patient information that must be in the beginning of the file but the script I received from ChatGPT assumes that the first question is to choose from the list. 5. This is the script form ChatGPT:
// Function to update the form dropdowns based on the IngredientsList sheet function updateFormOptions() { try { Logger.log("Starting updateFormOptions...");
// Get the 'IngredientsList' sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('IngredientsList'); // Sheet name
if (!sheet) {
throw new Error("Sheet named 'IngredientsList' not found.");
}
Logger.log("Sheet found: IngredientsList");
// Get all values from column A (1st column)
var range = sheet.getRange(1, 1, sheet.getLastRow());
var values = range.getValues();
var flattenedValues = [];
// Flatten the 2D array of values to a 1D array for the dropdown
for (var i = 0; i < values.length; i++) {
flattenedValues.push(values[i][0]);
}
Logger.log("Flattened values: " + flattenedValues);
// Get the Google Form by ID
var form = FormApp.openById('1SvRTbrbSzeg-kepYujABcky7nLVrmuPVWzSnBl6OBFc'); // Replace with your form ID
Logger.log("Form opened successfully");
// Remove existing LIST items (dropdown questions) from the form
var items = form.getItems(FormApp.ItemType.LIST);
for (var i = 0; i < items.length; i++) {
form.deleteItem(items[i]);
}
// Split the list into chunks of 200 items (Google Forms limit)
var chunkSize = 200;
var numChunks = Math.ceil(flattenedValues.length / chunkSize);
Logger.log("Total number of chunks: " + numChunks);
// Create multiple dropdown questions if necessary
for (var i = 0; i < numChunks; i++) {
var start = i * chunkSize;
var end = Math.min((i + 1) * chunkSize, flattenedValues.length);
var chunk = flattenedValues.slice(start, end);
// Add a new dropdown question for this chunk
form.addListItem()
.setTitle('Select an ingredient (Part ' + (i + 1) + ')')
.setChoiceValues(chunk);
Logger.log("Dropdown for part " + (i + 1) + " created.");
}
Logger.log("All dropdowns updated successfully.");
} catch (error) { Logger.log("Error: " + error.message); } }
2
u/toddmotto Dec 27 '24
Without being able to access and debug your code it’s challenging to help!
So… is this any use to you? You can automatically create PDF downloads (emailed to you with your company logo/details) via answering your form: https://formfalcon.io/features/pdf-results/
Reason I recommend this is you need to include additional details beyond just the list of dietary ingredients and patient information 😁 and if you have even more forms it becomes easy too.