Request to a public Google Form returns Access Denied: HTTP

user_4630650 0
Viewed 351

I am trying to use MS Excel to automate the filling up of the Google Form, but I have problem with access, the same code was working before and I did not change anything. here is the code:

Sub postGoogleData()
Dim params As String, url As String

url = "https://docs.google.com/forms/u/0/d/e/1FAIpQLSejadFloQF5V69ZZ1fc1kManOZ_vMw8kq10-56ENroZ4c6xiw/formResponse"
params = "entry.1880992192=<NAME>&entry.2125520249=<IG>&entry.1421191722=<E>&entry.1858469755=Option+1&entry.1858469755=Option+2&dlut=1632409237551&entry.1858469755_sentinel=&fvv=1&draftResponse=%5Bnull%2Cnull%2C%22-829268773860757612%22%5D&pageHistory=0&fbzx=-829268773860757612"

params = Replace(params, "<NAME>", Range("A1").Value)
params = Replace(params, "<IG>", Range("A2").Value)
params = Replace(params, "<E>", Range("A3").Value)
  
' add a reference to Microsoft XML 6.0 (tools...reference...microsoft XML v6.0_

Dim http As New MSXML2.XMLHTTP60

http.Open "POST", url, False
http.setRequestHeader "content-type", "application/x-www-form-urlencoded"
http.send params

End Sub

I received the error Access Denied. I changed the Internet Explorer Options: Internet Options Security > Custom Security Level > Miscellaneous > Access Data Sources Across Domains > Enable. This removed the error but my Google Form wasn't populated.

I have tried MSXML2.ServerXMLHTTP60 but this didn't populate the Google Form either.

Here is the spreadsheet that contains the summary of all the responses. Here is the Google Form. My objective is to use Excel to automate the filling up of the Google Form. This code used to work before but I encountered the Access Denied error in files that used to work before. Could it be because of changes that Google must have implemented in their web-services?

I appreciate your help. Thank you.

google-forms vba excel
2 Answers

Seems that Google blocks requests without User-Agent so you need to add something like

http.setRequestHeader "User-Agent", "my generic vba script"

before you send the parameters. Or you fake any existing user agent like

http.setRequestHeader "User-Agent", "Mozilla/5.0 (iPad; U; CPU OS 3_2_1 like Mac OS X; en-us) AppleWebKit/531.21.10 (KHTML, like Gecko) Mobile/7B405"

After adding that line the answer appears in the spreadsheet:

enter image description here

Use it like

http.Open "POST", url, False
http.setRequestHeader "User-Agent", "my generic vba script"
http.setRequestHeader "content-type", "application/x-www-form-urlencoded"
http.send params

As you can see in the screen recording below it works. I just copied your code added the reference to Microsoft XML 6.0, added the user agent line and run it.

enter image description here

user_32196130

you should set app settings to the values below:

screen shot

If any of these options are on, the use will be required to login with google account, this is why you are getting Access Denied

user_169302390
Related