I have a google form in which i want to extract different dropdown values from different google sheet

[Recrust International]

Hi Folks, I am trying to get values from a list of google sheet through a google script in google form editor, It is only extracting values from a single list and script identifies it by Sheet ID and Sheet name. Google question name is same as header name in the google sheet. The code is given below.

function openForm(e)

{

populateQuestions();

}

function populateQuestions() {

var form = FormApp.getActiveForm();

var googleSheetsQuestions = getQuestionValues();

var itemsArray = form.getItems();

itemsArray.forEach(function(item){

googleSheetsQuestions[0].forEach(function(header_value, header_index) {

if(header_value == item.getTitle())

{

var choiceArray = [];

for(j = 1; j < googleSheetsQuestions.length; j++)

{

(googleSheetsQuestions[j][header_index] != '') ? choiceArray.push(googleSheetsQuestions[j][header_index]) : null;

}

item.asListItem().setChoiceValues(choiceArray);

// If using MCQ Questions use line below instead of line above.

//item.asMultipleChoiceItem().setChoiceValues(choiceArray);

}

});

});

}

function getQuestionValues() {

var ss= SpreadsheetApp.openById('<Enter sheet ID>');

var questionSheet = ss.getSheetByName('<Enter sheet Name>');

var returnData = questionSheet.getDataRange().getValues();

return returnData;

}

But now i have a google form in which multiple questions drop down data needs to get extracted from multiple google sheets, Can anyone help me for the code? I have already tried below code but its not working for me, Can anyone please help me in performing this task?

function openForm(e) {

populateQuestions();

}

function populateQuestions() {

var form = FormApp.getActiveForm();

var configSheet = getConfigurationSheet();

configSheet.forEach(function(configRow) {

var formItemTitle = configRow[0];

var sheetId = configRow[1];

var sheetName = configRow[2];

var googleSheetsQuestions = getQuestionValues(sheetId, sheetName);

if (!googleSheetsQuestions) {

Logger.log("Error: Could not retrieve questions from sheet ID: " + sheetId + ", Name: " + sheetName);

return;

}

var itemsArray = form.getItems();

itemsArray.forEach(function(item) {

if (item.getTitle() == formItemTitle) {

var choiceArray = [];

for (var j = 1; j < googleSheetsQuestions.length; j++) {

if (googleSheetsQuestions[j][0] != '') {

choiceArray.push(googleSheetsQuestions[j][0]);

}

}

try {

if (item.getType() == FormApp.ItemType.LIST) {

item.asListItem().setChoiceValues(choiceArray);

} else if (item.getType() == FormApp.ItemType.MULTIPLE_CHOICE) {

item.asMultipleChoiceItem().setChoiceValues(choiceArray);

} else {

Logger.log("Error: Item type " + item.getType() + " is not supported.");

}

} catch (error) {

Logger.log("Error setting choices for item: " + item.getTitle() + ". Check if the question type matches. Error: " + error);

}

}

});

});

}

function getConfigurationSheet() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

Logger.log("Spreadsheet: " + ss); // Debugging line

if (!ss) {

Logger.log("Error: Spreadsheet is null. Check if the script is bound to the spreadsheet.");

return []; // Important: Return an empty array to prevent further errors

}

var configSheet = ss.getSheetByName('Config');

if (!configSheet) {

Logger.log("Error: Configuration sheet 'Config' not found.");

return [];

}

var returnData = configSheet.getDataRange().getValues();

returnData.shift(); // Remove header row

return returnData;

}

function getQuestionValues(sheetId, sheetName) {

try {

var ss = SpreadsheetApp.openById(sheetId);

var questionSheet = ss.getSheetByName(sheetName);

if (!questionSheet) {

Logger.log("Error: Sheet '" + sheetName + "' not found in spreadsheet ID: " + sheetId);

return null;

}

var returnData = questionSheet.getDataRange().getValues();

return returnData;

} catch (error) {

Logger.log("Error opening spreadsheet or sheet: " + error);

return null;

}

}

Using MacOS, Business and using google chrome as brouser

[Phobia Cide]

Potential Issues:

1. Configuration Sheet Read Issues:

   • Ensure the 'Config' sheet exists and has the correct structure:
     • Column A: Form Question Title
     • Column B: Google Sheet ID
     • Column C: Google Sheet Name
   • If any of these values are missing or incorrect, the script may fail.

2. Incorrect Handling of getQuestionValues() Return Values:

   • If getQuestionValues() returns null, attempting to process it leads to errors.
   • Add additional checks to prevent breaking on missing data.

3. Mismatch Between Form Items & Sheet Data:

   • Ensure form question titles exactly match the headers in the sheets.
   • Trim whitespace and check for case sensitivity issues.

---

Fixed Code with Improved Debugging

This version includes better error handling, logging, and ensures compatibility with multiple sheets.

/** * Opens and populates Google Form dropdowns from multiple Google Sheets. */
function openForm(e) {
    populateQuestions();
} /** * Populates Google Form questions using multiple sheets defined in the 'Config' sheet. */

function populateQuestions() {
    var form = FormApp.getActiveForm();
    var configSheet = getConfigurationSheet();

    if (configSheet.length === 0) {
        Logger.log("No configuration found. Ensure the 'Config' sheet exists and is correctly formatted.");

        return;
    }
    var itemsArray = form.getItems();

    configSheet.forEach(function(configRow) {
        var formItemTitle = configRow[0].trim(); // Trim to avoid spaces
        var sheetId = configRow[1].trim();
        var sheetName = configRow[2].trim();
        Logger.log("Processing question: " + formItemTitle + " from Sheet ID: " + sheetId + ", Name: " + sheetName);

        var googleSheetsQuestions = getQuestionValues(sheetId, sheetName);

        if (!googleSheetsQuestions || googleSheetsQuestions.length < 2) {
            Logger.log("Warning: No valid data found in sheet '" + sheetName + "' for question '" + formItemTitle + "'");
            return;
        }
        itemsArray.forEach(function(item) {
            if (item.getTitle().trim() === formItemTitle) {

                var choiceArray = [];
                for (var j = 1; j < googleSheetsQuestions.length; j++) {

                    var choice = googleSheetsQuestions[j][0]; // Adjust index if multiple columns exist
                    if (choice && choice.toString().trim() !== '') {
                        choiceArray.push(choice.toString().trim());

                    }
                }
                try {
                    if (item.getType() == FormApp.ItemType.LIST) {
                        item.asListItem().setChoiceValues(choiceArray);
                    } else if (item.getType() == FormApp.ItemType.MULTIPLE_CHOICE) {
                        item.asMultipleChoiceItem().setChoiceValues(choiceArray);
                    } else {

                        Logger.log("Warning: Item type " + item.getType() + " not supported for " + formItemTitle);
                    }
                } catch (error) {
                    Logger.log("Error setting choices for " + formItemTitle + ": " + error);
                }
            }
        });
    });
}

/**
 * Retrieves the configuration sheet data from the active spreadsheet.
 * @returns {Array<Array<string>>} Configuration rows without headers.
 */

function getConfigurationSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var configSheet = ss.getSheetByName('Config');
    if (!configSheet) {

        Logger.log("Error: 'Config' sheet not found in the spreadsheet.");

        return [];
    }
    var returnData = configSheet.getDataRange().getValues();
    returnData.shift(); // Remove header row return returnData; }

    /**
     * Retrieves question values from a specified Google Sheet by ID and sheet name.
     * @param {string} sheetId - The ID of the Google Sheet.
     * @param {string} sheetName - The name of the sheet within the Google Sheet.
     * @returns {Array<Array<string>>|null} The question data, or null if an error occurs.
     */

    function getQuestionValues(sheetId, sheetName) {
        try {
            var ss = SpreadsheetApp.openById(sheetId);
            var questionSheet = ss.getSheetByName(sheetName);
            if (!questionSheet) {
                Logger.log("Error: Sheet '" + sheetName + "' not found in spreadsheet ID: " + sheetId);
                return null;
            }
            var returnData = questionSheet.getDataRange().getValues();

            if (returnData.length === 0) {
                Logger.log("Warning: Sheet '" + sheetName + "' is empty.");
                return null;
            }
            return returnData;
        } catch (error) {
            Logger.log("Error retrieving data from sheet '" + sheetName + "': " + error);
            return null;
        }
    }

---

Key Improvements

✅ Trims extra spaces: Prevents errors from accidental spaces in sheet names or titles.
✅ Prevents overwriting empty fields: Avoids blank values being mistakenly added.
✅ Better logging & debugging: Logger.log() messages help track which sheets & questions are processed.
✅ Handles missing/empty sheets gracefully: Instead of crashing, it logs errors and skips the issue.
✅ Flexible for multiple sheets: Works with any number of sheets, as long as 'Config' is correctly set up.

[Brent Guttmann]

+1 For chatGPT, lol.

I am assuming you're good now, but another approach would be to combine the data from the various sheets into 1 so that you only have to retrieve it once. Since you're newer, this may (or may not) be easier for you to make changes down the road should you need to. Either way, options are good!

=ARRAYFORMULA({

   IMPORTRANGE("URL_of_Workbook2", "Sheet2!A:A"),

   IMPORTRANGE("URL_of_Workbook3", "Sheet3!B:B"),

   IMPORTRANGE("URL_of_Workbook4", "Sheet4!C:C"),

   IMPORTRANGE("URL_of_Workbook5", "Sheet5!D:D")

})

[NILESH KUMAR]

Can anyone help me I want a store whatsapp message  incoming and outgoing both stores in  Google sheet I have api messageautosender.com

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/9d168f4f-3079-47f4-8afe-c1dff64158a4n%40googlegroups.com.

[Brent Guttmann]

You've got to try first.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/bfkvT360qzo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BOTw%3DFa%3DjqtLoyA8mH9fdDLn_Wyk50Af4d_xCUfoLeqZ3w86A%40mail.gmail.com.