I have a normal Google Form that is filling answers into a response spreadsheet but I want to import pre-existing data stored on another Google Sheet (100 entries) that match the Google Form responses each week (same data) without typing each one manually.
I have figured out how to get most of the data from the existing/imported sheet into the response form/sheet but the date, grid and list items will not convert properly and are blank so I need help with the code to fix that. Here is what I have already. I am certainly a novice so all help is helpful!
function FillFormfromSpreadSheet() {
var ss = SpreadsheetApp.openById('1W6rU4KuQcsEaTRgz-bhtibyiJXQs8-kviOihuV3pAcQ');//Spreasdsheet ID
var sheet = ss.getSheetByName("Weekly Repeat TEST 2");
var form = FormApp.openById('1R9pefkT-bbh40ftNIPVwMQBuPbiYTysxiiANt3LndsQ');//'Form ID'
var data = sheet.getDataRange().getValues(); // Data to fill
var EmptyString = '';
var items = form.getItems();
for (var row = 1; row < data.length; row++ ) { //jumps first row, containing headers
var response = form.createResponse();
for (var i=0; i<items.length; i++) {//using 'i' to count both data fields and form items
var resp = data[row][i+1];//jumps first data field containing timestamp
// Need to treat every type of answer as its specific type.
if (resp !== EmptyString ) {//jumps the entire procedure for empty datafields, as may occur for non required questions
switch (items[i].getType()) {//note that data[#][1] corresponds to item[0], as there's no timestamp item!
case FormApp.ItemType.MULTIPLE_CHOICE:
item = items[i].asMultipleChoiceItem();
break;
case FormApp.ItemType.CHECKBOX:
item = items[i].asCheckboxItem();
// In a form submission event, resp is an array, containing CSV strings. Join into 1 string.
// In spreadsheet, just CSV string. Convert to array of separate choices, ready for createResponse().
if (typeof resp !== 'string')
resp = resp.join(','); // Convert array to CSV
resp = resp.split(/ *, */); // Convert CSV to array
break;
case FormApp.ItemType.TEXT:
var item = items[i].asTextItem();
break;
case FormApp.ItemType.PARAGRAPH_TEXT:
item = items[i].asParagraphTextItem();
break;
case FormApp.ItemType.Date:
item = items[i].asDateItem()
returnDate(i);
break;
case FormApp.ItemType.List:
item = items[i].asListItem();
break;
case FormApp.ItemType.Grid:
item = items[i].asGridItem();
break;
default:
item = null; // Not handling IMAGE, PAGE_BREAK, SCALE, SECTION_HEADER,
break;
}
if (item) {// Add this answer to form
var respItem = item.createResponse(resp);
response.withItemResponse(respItem)
}
}
}
response.submit();
Utilities.sleep(500);
}
}