Google-sheets – Importing data into Google Form response sheet with date, list, grid

google sheetsgoogle-apps-scriptgoogle-forms

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

Best Answer

i know this is an old post but this answer is for anyone else looking for the same solution to a similar problem quite recently.

Working directly on the actual google forms responses sheet is not advisable as google forms will overrite any preexisiting data and replace it with the form responses answers - be it text formulas or even cell formatting - theres a simple fix for this - do not work directly on that sheet - just bring that data to a new sheet & then your codes & formulas will work just fine be it scripting code or simple vlookup formulas to colatlate your data.

I use a simple workaround to bypass the google form overwriting formatting & deleting formulas & other data on the form responses answer sheet on each new entry.

With this solution i will show you - you get two benifits

1) you can retain any cell formatting you want - this method allows you to do that conveniently

2) and most importantly you can use formulas safely without the fear of the formulas being deleted on each new form response entry - as you are aware google forms creates a new entry on the next available row irrespective if you already prefilled formulas in any other collumn or rows on that response sheet - thsi is especially usefull if your form collects numerical data that needs to be summed up etc. & needs to be used for pviots & charts in real time

the solution is simple - on a corresponding new sheet just use "=arrayformula" & refrence the whole form responses answer sheet data range.

What you get on your new sheet is the exact replication of your form responses data sheet that will autoupdate with each new form response & with the added benift of using unlimited formatting & unlimited formulas without the hassel of losing the same by google forms overwriting rows & deleting formatting & deleting formulas.

Related Topic