1

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);
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.