google-sheets-to-forms

/**
script adapted from https://www.youtube.com/watch?v=pXUsW6VRQak
 */
function myFunction() {
    var sheetName = 'Sheet1';
    var formId = '1LnmQRJX0l586EvNeXYNLujO2vqhqB-Grtfe1J5zfFWI';
    var title = 'Form Title here';
    var description = 
        'Form description here.' +
        'This should really be moved to a cell in the sheet.' +
        '\n\n' +
        'Notice how two new-lines are needed in the text for paragraphs - move to a cell.';

    // use this to populate the selections of Column A/B in the spreadsheet.
    var fieldTypes = 'TEXT,PARAGRAPH,CHOICE,CHECKBOX,LIST,GRID,IMAGE,PAGE,SECTION,TIME';
    var isRequired = 'yes,no';
    
    var column = {
        'TYPE': 0,
        'REQUIRED': 1,
        'QUESTION': 2,
        'DESCRIPTION': 3,
        'CHOICE A': 4, // can go on.
        'CHOICE B': 5,
        'CHOICE C': 6,
        'CHOICE D': 7,
        'CHOICE E': 8,
    }
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    var range = ss.getDataRange();
    var data = range.getValues();
    var numberRows = range.getNumRows();
    var numberColumns = range.getNumColumns();

    var form = FormApp.openById(formId);
    form.setTitle(title);
    form.setDescription(description);

    // delete all questions in the form
    form.getItems().forEach(function (item) {
        form.deleteItem(item);
    });

    for (var i = 0, currRow = 1; i < numberRows; i++, currRow++) {
        var questionType = data[currRow][column.TYPE];
        switch (questionType) {
            case 'TEXT':
                {
                    var formItem = form.addTextItem()
                        .setTitle(data[i][column.QUESTION])
                        .setRequired(column.REQUIRED === 'yes')
                    var description = data[i][column.DESCRIPTION];
                    if (!!description) formItem.setHelpText(description);
                    break;
                }
            case 'PARAGRAPH':
                {
                    var formItem = form.addParagraphTextItem()
                        .setTitle(data[i][column.QUESTION])
                        .setRequired(column.REQUIRED === 'yes');
                    var description = data[i][column.DESCRIPTION];
                    if (!!description) formItem.setHelpText(description);
                    break;
                }
            case 'CHOICE':
                {
                    var rowLength = data[i].length;
                    var currentRow = firstRow + i;
                    var currentRangeValues = ss.getRange(currentRow, 1, 1, rowLength).getValues();
                    var getSheetRange = ss.getDataRange();
                    var numberOfColumnsSheet = getSheetRange.getNumColumns();
                    var numberOfOptionsInCurrentRow = numberOfColumnsSheet;
                    var lastColumnInRange = String.fromCharCode(64 + (numberOfOptionsInCurrentRow));
                    var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
                    var optionsArray = ss.getRange(range_string).getValues();
                    var choicesForQuestion = [];
                    for (var j = 0; j < optionsArray[0].length; j++) {
                        if (!!optionsArray[0][j]) {
                            choicesForQuestion.push(optionsArray[0][j]);
                        }
                    }
                    var formItem = form.addMultipleChoiceItem()
                        .setTitle(data[i][column.QUESTION])
                        .setHelpText(data[i][column.DESCRIPTION])
                        .setChoiceValues(choicesForQuestion)
                        .setRequired(data[i][column.REQUIRED] === 'yes');
                    var description = data[i][column.DESCRIPTION];
                    if (!!description) formItem.setHelpText(description);
                    break;
                }
            case 'CHECKBOX':
                {
                    var rowLength = data[i].length;
                    var currentRow = firstRow + i;
                    var currentRangeValues = ss.getRange(currentRow, 1, 1, rowLength).getValues();
                    var getSheetRange = ss.getDataRange();
                    var numberOfColumnsSheet = getSheetRange.getNumColumns();
                    var numberOfOptionsInCurrentRow = numberOfColumnsSheet;
                    var lastColumnInRange = String.fromCharCode(64 + (numberOfOptionsInCurrentRow));
                    var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
                    var optionsArray = ss.getRange(range_string).getValues();
                    var choicesForQuestion = [];
                    for (var j = 0; j < optionsArray[0].length; j++) {
                        if (!!optionsArray[0][j]) {
                            choicesForQuestion.push(optionsArray[0][j]);
                        }
                    }
                    var formItem = form.addCheckboxItem()
                        .setTitle(data[i][column.QUESTION])
                        .setChoiceValues(choicesForQuestion)
                        .setRequired(data[i][column.REQUIRED] === 'yes');
                    var description = data[i][column.DESCRIPTION];
                    if (!!description) formItem.setHelpText(description);
                    break;
                }
            case 'LIST':
                {
                    var rowLength = data[i].length;
                    var currentRow = firstRow + i;
                    var currentRangeValues = ss.getRange(currentRow, 1, 1, rowLength).getValues();
                    var getSheetRange = ss.getDataRange();
                    var numberOfColumnsSheet = getSheetRange.getNumColumns();
                    // var numberOfOptionsInCurrentRow = numberOfColumnsSheet;
                    // var lastColumnInRange = String.fromCharCode(64 + (numberOfOptionsInCurrentRow));
                    // var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
                    // var optionsArray = ss.getRange(range_string).getValues();
                    var choicesForQuestion = [];
                    for (var j = 4; j < numberOfColumnsSheet; j++) {
                        const cell = data[currentRow][j];
                        if (cell) choicesForQuestion.push(cell);
                    }
                    var formItem = form.addListItem()
                        .setTitle(data[i][column.QUESTION])
                        .setChoiceValues(choicesForQuestion)
                        .setRequired(data[i][column.REQUIRED] === 'yes');
                    // var description = data[i][column.DESCRIPTION];
                    var description = 'numberOfColumnsSheet: ' + numberOfColumnsSheet + ' range_string:' + range_string + ' currentRow: ' + currentRow;
                    if (!!description) formItem.setHelpText(description);
                    break;
                }
            case 'GRID':
                {
                    var rowLength = data[i].length;
                    var currentRow = firstRow + i;
                    var currentRangeValues = ss.getRange(currentRow, 1, 1, rowLength).getValues();
                    var getSheetRange = ss.getDataRange();
                    var numberOfColumnsSheet = getSheetRange.getNumColumns();
                    var numberOfOptionsInCurrentRow = numberOfColumnsSheet;
                    var lastColumnInRange = String.fromCharCode(64 + (numberOfOptionsInCurrentRow));
                    var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
                    var optionsArray = ss.getRange(range_string).getValues();
                    var rowTitles = [];
                    for (var j = 0; j < optionsArray[0].length; j++) {
                        if (!!optionsArray[0][j]) {
                            rowTitles.push(optionsArray[0][j]);
                        }
                    }
                    var rowLength = data[i + 1].length;
                    var currentRow = firstRow + i + 1;
                    var currentRangeValues = ss.getRange(currentRow, 1, 1, rowLength).getValues();
                    var getSheetRange = ss.getDataRange();
                    var numberOfColumnsSheet = getSheetRange.getNumColumns();
                    var numberOfOptionsInCurrentRow = numberOfColumnsSheet;
                    var lastColumnInRange = String.fromCharCode(64 + (numberOfOptionsInCurrentRow));
                    var range_string = 'E' + currentRow + ":" + lastColumnInRange + currentRow;
                    var optionsArray = ss.getRange(range_string).getValues();
                    var columnTitles = [];
                    for (var j = 0; j < optionsArray[0].length; j++) {
                        if (!!optionsArray[0][j]) {
                            columnTitles.push(optionsArray[0][j]);
                        }
                    }
                    var formItem = form.addGridItem()
                        .setTitle(data[i][column.QUESTION])
                        .setRows(rowTitles)
                        .setColumns(columnTitles)
                        .setRequired(data[i][column.REQUIRED] === 'yes');
                    var description = data[i][column.DESCRIPTION];
                    if (!!description) formItem.setHelpText(description);
                    break;
                }
            case 'IMAGE':
                {
                    var img = UrlFetchApp.fetch(data[i][3]);
                    var formItem = form.addImageItem()
                        .setTitle(data[i][column.QUESTION])
                        .setImage(img);
                    var description = data[i][column.DESCRIPTION];
                    if (!!description) formItem.setHelpText(description);

                    break;
                }
            case 'PAGE':
                {
                    var formItem = form.addPageBreakItem()
                        .setTitle(data[i][column.QUESTION]);
                    var description = data[i][column.DESCRIPTION];
                    if (!!description) formItem.setHelpText(description);
                    break;
                }
            case 'SECTION':
                {
                    var formItem = form.addSectionHeaderItem()
                        .setTitle(data[i][column.QUESTION]);
                    var description = data[i][column.DESCRIPTION];
                    if (!!description) formItem.setHelpText(description);
                    break;
                }
            case 'TIME':
                {
                    var formData = form.addTimeItem()
                        .setTitle(data[i][column.QUESTION]);
                    var description = data[i][column.DESCRIPTION];
                    if (!!description) formItem.setHelpText(description);
                }
        }
    }
}