~bendersteed/gsheets-to-gforms

ref: 9629a8f4dcecc0f381c202b41d9fee77bfef4cd2 gsheets-to-gforms/append-responses.gs -rw-r--r-- 3.3 KiB
9629a8f4 — Dimakakos Dimos Add: most work done for now 2 years ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
function AppendResponses() {
    // get spreadsheet data  
    const sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1JV3BUiZ1t2N1NiX-yW-XMxEgo-qOVVSifjFVQVMe24E/edit");
    const rows = sheet.getDataRange();
    const numRows = rows.getNumRows();
    const values = rows.getValues();
    
    // get form data
    const form = FormApp.openByUrl("https://docs.google.com/forms/d/1IVrUpV_cIljb-EzN2_JUMXlTo-UozDQPOpRz_dh7m_s/edit");
    const items = form.getItems().filter(i => i.getType() != "SECTION_HEADER"); // filter out section headers, since these get no answers
    
    // for every row in values ...
    for (let v = 1; v < values.length; v ++) {
        const value = values[v];
        const formResponse = form.createResponse(); // the response object we will submit
        let data_index = 1; // we will increment on this, but we don't want the first column, since it's the date!
        
        // ... take each element and make it a response for the corresponding item of the form
        // CHECKBOX answers are in a single cell as comma separated values
        // GRID answers take multiple cells, each for every row, so we need to keep track of the column position
        for (let i = 0; i < items.length; i++) {
            const item = items[i];
            const type = item.getType();
            
            if (type == "TEXT") {
                const textItem = item.asTextItem();
                const response = textItem.createResponse(value[data_index]);
                formResponse.withItemResponse(response);
                data_index++;
                
            } else if (type == "PARAGRAPH_TEXT") {
                const paragraphTextItem = item.asParagraphTextItem();
                const response = paragraphTextItem.createResponse(value[data_index]);
                formResponse.withItemResponse(response);
                data_index++;
                
            } else if (type == "MULTIPLE_CHOICE") {
                const multipleChoiceItem = item.asMultipleChoiceItem();
                const response = multipleChoiceItem.createResponse(value[data_index]);
                formResponse.withItemResponse(response);
                data_index++;
                
            } else if (type == "CHECKBOX") {
                const arr = value[data_index].split(',').map(x => x.trim());
                // since we split on commas, it's thoughtfull to replace commas in user submitted answers with dashes
                const checkboxItem = item.asCheckboxItem();
                const response = checkboxItem.createResponse(arr);
                formResponse.withItemResponse(response);
                data_index++;
                
            } else if (type == "GRID") {
                const gridItem = item.asGridItem();
                const gridHeight = gridItem.getRows().length;
                const arr = value.slice(data_index, data_index + gridHeight);
                const response = gridItem.createResponse(arr);
                formResponse.withItemResponse(response);
                data_index += gridHeight;
            }
            else {
                throw new Error('This kind of item in not implemented. DIY or drop a line at me@bendersteed.tech');
            }          
        }
        formResponse.submit();
        Utilities.sleep(500);   
    }
};