Filling QC Google forms with many pages using Google Sheet- Only Last line of code not working i.e. UrlFetchApp

Question

I have learned how to automate google form filling using google sheet and app script. After many trial and error to apply the concept and automate it on simple google forms i have created, it worked absolutely fine.

But when I applied on the google form (which i seriously want to automate), everything works fine except the last line of code i.e. UrlFetchApp which brings google form link and data together and submit the form using "post" method but it's not working.

When i commented this line rest of the code executed without any error but this line is making my effort fail.

google form link for which i have created this code: https://docs.google.com/forms/d/e/1FAIpQLSc1uSOspwlen5Tcs0ccTxE4eia-scw3aVUwAsrVMdQBpk5ydA/viewform

function QC_form_fill() {

  var wrkBk = SpreadsheetApp.getActiveSpreadsheet();
  var wrkSht = wrkBk.getSheetByName("Qdata");

var formURL =""
var formData =""

var sdate = "";
var stime = "";
var mname = "";
var board = "";
var eclas = "";
var batch = "";
var slecture = "";
var sfaculty = "";
var ati1 = "";
var ta1 = "";
var ta2 = "";
var ta3 = "";
var sopen = "";
var stengage = "";
var teachpace = "";
var chtresp = "";
var tmmanmnt = "";
var vomod = "";
var sgrammar = "";
var qppt = "";
var fhndwrtng = "";
var drssup = "";
var lobjective = "";
var expstyl = "";
var sflow = "";
var cntnacc = "";
var demos = "";
var ati2 = "";
var poll = "";
var la = "";
var hw = "";
var prlfcd = "";
var sggstn = "";

var noOfRows = wrkSht.getRange("AI1").getDisplayValue();

for (i=2; i<=noOfRows; i++)
{
  sdate =wrkSht.getRange("A"+i).getDisplayValue();
  stime =wrkSht.getRange("B"+i).getDisplayValue();
mname =wrkSht.getRange("C"+i).getDisplayValue();
board =wrkSht.getRange("D"+i).getDisplayValue();
eclas =wrkSht.getRange("E"+i).getDisplayValue();
batch =wrkSht.getRange("F"+i).getDisplayValue();
slecture =wrkSht.getRange("G"+i).getDisplayValue();
sfaculty =wrkSht.getRange("H"+i).getDisplayValue();
ati1 =wrkSht.getRange("I"+i).getDisplayValue();
ta1 =wrkSht.getRange("J"+i).getDisplayValue();
ta2 =wrkSht.getRange("K"+i).getDisplayValue();
ta3 =wrkSht.getRange("L"+i).getDisplayValue();
sopen =wrkSht.getRange("M"+i).getDisplayValue();
stengage =wrkSht.getRange("N"+i).getDisplayValue();
teachpace =wrkSht.getRange("O"+i).getDisplayValue();
chtresp =wrkSht.getRange("P"+i).getDisplayValue();
tmmanmnt =wrkSht.getRange("Q"+i).getDisplayValue();
vomod =wrkSht.getRange("R"+i).getDisplayValue();
sgrammar =wrkSht.getRange("S"+i).getDisplayValue();
qppt =wrkSht.getRange("T"+i).getDisplayValue();
fhndwrtng =wrkSht.getRange("U"+i).getDisplayValue();
drssup =wrkSht.getRange("V"+i).getDisplayValue();
lobjective =wrkSht.getRange("W"+i).getDisplayValue();
expstyl =wrkSht.getRange("X"+i).getDisplayValue();
sflow =wrkSht.getRange("Y"+i).getDisplayValue();
cntnacc =wrkSht.getRange("Z"+i).getDisplayValue();
demos =wrkSht.getRange("AA"+i).getDisplayValue();
ati2 =wrkSht.getRange("AB"+i).getDisplayValue();
poll =wrkSht.getRange("AC"+i).getDisplayValue();
la =wrkSht.getRange("AD"+i).getDisplayValue();
hw =wrkSht.getRange("AE"+i).getDisplayValue();
prlfcd =wrkSht.getRange("AF"+i).getDisplayValue();
sggstn =wrkSht.getRange("AG"+i).getDisplayValue();

 formURL ="https://docs.google.com/forms/d/e/1FAIpQLSc1uSOspwlen5Tcs0ccTxE4eia-scw3aVUwAsrVMdQBpk5ydA/formResponse?pageHistory=0,1"
 formData= "&entry.1809185170=" + sdate + "&entry.772722605=" + stime + "&entry.1721230767=" + mname + "&entry.1024240207=" + board + "&entry.1132896225=" + eclas +"&entry.134507089="+ batch + "&entry.1349051916=" + slecture + "&entry.15763511=" +sfaculty + "&entry.1915124723=" + ati1 + "&entry.1154530333=" + ta1 + "&entry.1450881147=" + ta2 + "&entry.1627300385=" + ta3 + "&entry.928964249=" + sopen + "&entry.103830616=" + stengage + "&entry.1749915137=" + teachpace + "&entry.1879781398=" + chtresp + "&entry.1210552578=" + tmmanmnt + "&entry.1659325247=" + vomod + "&entry.377007890=" + sgrammar + "&entry.1207703694=" + qppt + "&entry.960993930=" + fhndwrtng + "&entry.343485399=" + drssup + "&entry.1988728268=" + lobjective + "&entry.1019203670=" + expstyl + "&entry.222077844=" + sflow + "&entry.465124407=" + cntnacc +  "&entry.1299956408=" + demos + "&entry.956891355=" + ati2 + "&entry.60052266=" + poll + "&entry.125539625=" + la + "&entry.1193513376=" + hw + "&entry.705255491=" + prlfcd + "&entry.121026171=" +sggstn

  var finalURL = formURL + formData

  var options = {
     "method" : "post"
  };

     UrlFetchApp.fetch(finalURL, options);
}

}

Welcome to [so]. Please replace the code by a [mcve]. It's very likely that it's not necessary to declare so many variables and to read so many cells to do he basic debugging of your code. In your [mcve] be sure to log the HTTP response (`var response = UrlFetchApp.fetch(url, options)` include muteHttpExceptions: true in the options object) — Rubén, Mar 07 '22 at 02:24
Related https://stackoverflow.com/q/20410497/1595451 . By the way you might be relying in an old hack that stopped working several years ago.If that is the case, consider to use Google Apps Script to submit form response by using FormApp rather than UrlFetchApp. — Rubén, Mar 07 '22 at 02:29

Filling QC Google forms with many pages using Google Sheet- Only Last line of code not working i.e. UrlFetchApp

0 Answers0