Prefill a Google Form using data inside Google Spreadsheet

Question

I am not a programer, just a Google Docs user trying to make daily tasks easier. I have made some small scripts that are really helpful, say one click to send a notification email to other users. Now I have a tricky problem that I don't know how to solve, and after searching for days, still have no clue.

I am using a Google SpreadSheet. If I click the "Link", it should open a pre-designed Google Form, which has: WO, Name, and Engineer fields filled by this row's data. For example: Row 1 of the Google Form should be filled with 4478, AAAAAAAAandDavid` in the corresponding form fields. The user should be able to change them.

Thank you guys for correcting my poor English ^^

Here is so far I can do, from debug log, it can get correct data from SpreadSheet. The problem is I don't know how to run this function when user click the "Link" to open a Google Form.

    function Finish() {
  var sheetOpen = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Open ticket list");
  var dataOpen = sheetOpen.getDataRange().getValues();
  var cellOpen = sheetOpen.getActiveCell();
  var currentRow = cellOpen.getRow();
  var wo = sheetOpen.getRange(currentRow, 1).getValue();
  var name = sheetOpen.getRange(currentRow, 3).getValue();
  var omEng = sheetOpen.getRange(currentRow, 5).getValue();
  // Browser.msgBox('wo = '+ wo +' and name = '+ name +' and Engineer = '+ omEng);

  var form = FormApp.openById('formid');

  Logger.log('Form Title is '+ form.getTitle()); 

  var allItems = form.getItems();
  Logger.log('Total items in this form '+ allItems.length)

  var woId=0;
  var nameId=0;
  var omEngId=0;

  for (i=0; i < allItems.length ; ++i) {
    var itemTitle = allItems[i].getTitle();
    Logger.log('Item Title '+ itemTitle);

    if (itemTitle == 'wo') {
      woId = allItems[i].getId();
    }
  }

  for (i=0; i < allItems.length ; ++i) {
    var itemTitle = allItems[i].getTitle();
    Logger.log('Item Title '+ itemTitle);

    if (itemTitle == 'name') {
      nameId = allItems[i].getId();
    }
  }

  for (i=0; i < allItems.length ; ++i) {
    var itemTitle = allItems[i].getTitle();
    Logger.log('Item Title '+ itemTitle);

    if (itemTitle == 'Engineer') {
      omEngId = allItems[i].getId();
    }
  }


  var woItem = form.getItemById(woId).asTextItem();
  woItem.createResponse(wo);

  var nameItem = form.getItemById(nameId).asListItem();
  nameItem.createResponse(name);

  var omEngItem = form.getItemById(omEngId).asListItem();  
  omEngItem.createResponse(omEng);


}

Alou Liu Alou Liu · Accepted Answer · 2015-12-21T06:50:06

After the subject is changed to "Prefill xxx", I found a similar question with great answer here. I re-write the code like this, it works nice. Thank you all ^__^

function FinishReport() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Open ticket list");
  var data = ss.getDataRange().getValues();  // Data for pre-fill
  var headers = data[0];  
  var form = FormApp.openByUrl("https://form_url");
  var items = form.getItems();
  var urlCol = headers.indexOf("Finish Report");
  var stopRow = FindStopRowFromOpenTicket();


  // Skip headers, then build URLs for each row in Sheet1.
for (var i = 1; i < stopRow; i++ ) {
    // Create a form response object, and prefill it
    var formResponse = form.createResponse();

    // Prefill WO
    var formItem = items[0].asTextItem();
    var response = formItem.createResponse(data[i][0]);
    formResponse.withItemResponse(response);

    // Prefill Name
    formItem = items[3].asListItem();
    response = formItem.createResponse(data[i][2]);
    formResponse.withItemResponse(response);

    // Prefill Engineer
    formItem = items[4].asListItem();
    response = formItem.createResponse(data[i][4]);
    formResponse.withItemResponse(response);

    // Get prefilled form URL
    var url = formResponse.toPrefilledUrl();
    // Logger.log(url);

    if (urlCol >= 0) {
      var urlRange = sheet.getRange(i+1,urlCol+1).setValue(url);
    }

  }

  // Delete URL of closed ticket
  for (var j = stopRow + 1; j < stopRow + 10; j++) {

    var delUrlRange = sheet.getRange(j, urlCol+1).setValue("");

  } 

};

function FindStopRowFromOpenTicket(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Open ticket list");
  var data = sheet.getDataRange().getValues();
  for(var i = data.length-1 ; i >=0 ; i--){
    if (data[i][1] != null && data[i][1] != ''){
      var StopRow = i + 1;
      return StopRow ;
    }
  }
}

Prefill a Google Form using data inside Google Spreadsheet

1 Answers