So i try to make an script on to automate data entry from google spreadsheet to google forms but i always receiving this code error
"Request failed for returned code 400. Truncated server response: <!DOCTYPE html><html lang="en"><head><link rel="shortcut icon" sizes="16x16" href=" (use muteHttpExceptions option to examine full response) (line 25, file "Code")" Can somebody help me?
Here My code
function auto_data_entry() { var formURL=""; var wrkBk = SpreadsheetApp.getActiveSpreadsheet(); var wrkSht= wrkBk.getSheetByName("Sheet1"); var Transaction1 = wrkSht.getRange("A1").getValue(); var Transaction2 = wrkSht.getRange("A2").getValue(); var Transaction3 = wrkSht.getRange("A3").getValue(); var Transaction4 = wrkSht.getRange("A4").getValue(); var Transaction5 = wrkSht.getRange("A5").getValue(); var datamap={"entry.663378019":Transaction1, "entry.978525821":Transaction2, "entry.1636658367":Transaction3, "entry.1490939339":Transaction4, "entry.2066528728":Transaction5}; var options = { "method": "post", "payload": datamap }; UrlFetchApp.fetch(formURL, options); } Thanks!
21 Answer
Your main issue here is that you are trying to use the fetch method for a google form. Fetch works for making HTTP requests to websites so it will be overcomplicated to try to do this to a form as you would need to create a custom response on that side.
Automating data entries
An easier way of achieving what you want is to use the Form Response methods from Apps Script Google Form´s documentation for pre filling the form. This piece of code will automate data entries from google´s spreadsheet:
function auto_data_entry(){ var url='YOUR FORM URL'; var wrkBk = SpreadsheetApp.getActiveSpreadsheet(); var wrkSht= wrkBk.getSheetByName("Sheet1") var Transaction1 = wrkSht.getRange("A1").getValue(); var Transaction2 = wrkSht.getRange("A2").getValue(); var Transaction3 = wrkSht.getRange("A3").getValue(); var Transaction4 = wrkSht.getRange("A4").getValue(); var Transaction5 = wrkSht.getRange("A5").getValue(); var data = [Transaction1,Transaction2,Transaction3,Transaction4,Transaction5]; var form = FormApp.openByUrl(url); var questions = form.getItems(); //get the different questions you want to pre fill var FormResponse = form.createResponse(); //set a response from your response data array to the corresponding question until you fill the whole form for(i=0;i<5;i++){ var qt = questions[i].asTextItem(); var qr = qt.createResponse(data[i]); FormResponse.withItemResponse( qr ); } //create a prefilled form var preurl = FormResponse.toPrefilledUrl(); Logger.log(preurl); }NOTE: your pre filled form url is in the log.
Automating form submissions
Also, if you wish to automate form submissions from your spreadsheet data you can use this code:
function auto_submission() { var url='YOUR FORM URL'; var wrkBk = SpreadsheetApp.getActiveSpreadsheet(); var wrkSht= wrkBk.getSheetByName("Sheet1") var Transaction1 = wrkSht.getRange("A1").getValue(); var Transaction2 = wrkSht.getRange("A2").getValue(); var Transaction3 = wrkSht.getRange("A3").getValue(); var Transaction4 = wrkSht.getRange("A4").getValue(); var Transaction5 = wrkSht.getRange("A5").getValue(); var data = [Transaction1,Transaction2,Transaction3,Transaction4,Transaction5]; var form = FormApp.openByUrl(url); var questions = form.getItems(); //get the different questions you want to fill var FormResponse = form.createResponse(); //set a response from your response data array to the corresponding question until you fill the whole form for(i=0;i<5;i++){ var qt = questions[i].asTextItem(); var qr = qt.createResponse(data[i]); FormResponse.withItemResponse( qr ); } //submit the form FormResponse.submit(); }I hope this has helped you, let me know if you found any issues or if you do not understand anything.
