Trouble Writing to a Google Sheet

[egolombek]

I am trying to write from LiveCode to a Google Sheet. I found the following post on this topic from 2009: viewtopic.php?t=2649 But, when I try this approach, I get a "HTTP response 405" error in the result. Any ideas?

The Google Form I am using is: https://docs.google.com/forms/d/e/1FAIp ... sp=sf_link

Which writes data in: https://docs.google.com/spreadsheets/d/ ... sp=sharing

The code I am using is:

on mouseUp
put cd fld "fName" into tNam
put cd fld "fLove" into tLove

put "entry.0.single" into tFieldName1
put "entry.1.single" into tFieldName2

put tNam into tFieldValue1
put tLove into tFieldValue2


put libUrlFormData (tFieldName1, tFieldValue1,tFieldName2, tFieldValue2) into tDataToPost
post tDataToPost to URL ("https://docs.google.com/forms/d/e/1FAIp ... sp=sf_link")

answer the result


end mouseUp

Any help is most appreciated.

[bangkok]

Try to add

Code: Select all

libUrlSetSSLVerification false

before your POST

(notice that the URL has "https")

Which version of LC do you use ?

[egolombek]

Hi.

I tried adding libUrlSetSSLVerification false, but same result.

I am using 9.5.1 Indy.

Thanks for any help!

[simon.schvartzman]

Hi, try replacing the last part of the url

...JbXjA/viewform

by

...JbXjA/formresponse

Hope it helps

[egolombek]

Did it, but now I get a 404 error (i.e. cannot find the page).

[simon.schvartzman]

Hi, use the following (working) code and you should be good to go from here

on mouseUp

put "entry.1417781247=myName" into tField1
put "entry.128740753=chocolate" into tField2


-- set it up in such a way that will accept any language
-- and will be able to handle accents properly
get "Content-Type: application/x-www-form-urlencoded;charset=utf-8" & CRLF
set the httpHeaders to IT

put tField1 into tDataToPost
put "&" after tDataToPost
put tField2 after tDataToPost
post tDataToPost to URL ("https://docs.google.com/forms/d/e/1FAIp ... rmResponse")

answer the result

end mouseUp

Notes:
- don't ask me why but is important to keep the capital "R" in "formResponse"
- the entry numbers (1417781247 & 128740753) are found in the source code of the Form page by doing a Find on "entry"

name="entry.1417781247"
name="entry.128740753"

I find the possibility to write to Google Sheet very powerful / useful but worried about Google changing something on the future and suddenly it will stop working...

Have fun

[egolombek]

First of all, thank you so much for helping. I really appreciate it.

The code you sent works, which is awesome. But... I need a little more help understanding how to generalize things.

You said to search "Entry" in the source code of the form. I searched all 44 times the word entry appears, and did not come across the entry numbers. I did find them if I searched for the form question.

But, I guess my question is: is there a system? Like in the 2009 post I quoted, he talked about the entry numbers being: entry.0.single and then going up by 1.

I'd like to make a library of functions for working with Google Forms/sheets, but searching the source code seems like doing so would be impossible. And, my little test form had 2 entries. I can imagine searching a form with 50 entries would be more than a little tedious. Is there a better way? (Or better yet... has anyone already written such a library?)

Thanks again for your help!

[mrcoollion]

Interesting stuff.
However, if I search the source code of the page I only get 2 x entry with the correct numbers.
In Windows with Google Chrome See source code by right click on the form page and choose 'View Page Source'.

You can also right click in the field and choose inspect. On the right side of the window in you browser, you will see the field related code and the entry with number.

---

[egolombek]

Thanks - I was looking on a different page -- not the "View Form" page.

You have definitely helped. Thank you.

I am still wondering, however, if there is an alternate method that does not depend on picking out names from source code. As well as being tedious, it is not very generalizable (is that a word?) and I would love to be able to create a library of some kind. Maybe there is a way to "look up" the name based on the question number? Other ideas?

[mrcoollion]

For learning and as a challenge, I build a small stack that is able to filter out the fields and numbers from a google sheet.
So when I got that working I thought 'Now I am home free because now I only need to copy-paste Simon's code and it works'
Well I was wrong .
Somehow I can not put my text into the Google Sheet Form. I get no message so I am stuck,

Would appreciate to find out what I am doing wrong Simon? So I attached the Stack to this post.

PS. Stack code is still very rough

Regards,

Paul

GoogleFormTest_v001.zip

(3.46 KiB) Downloaded 191 times

[egolombek]

Your button that retrieves the numbers is so nice!

The little thing in your code that is missing is that if you have more than one word in the data you are inputting, it must replace a + with the space.

So, try using libUrlFormData to automatically format things correctly:

put "entry.1417781247" into tFieldName1
put "entry.128740753" into tFieldName2

put tNam into tFieldValue1
put tLove into tFieldValue2


put libUrlFormData (tFieldName1, tFieldValue1,tFieldName2, tFieldValue2) into tDataToPost
post tDataToPost to URL ("https://docs.google.com/forms/d/e/1FAIp ... rmResponse")

[mrcoollion]

I am probably missing some info. How can I see that the form has been filled with my text? i do not see anything changing in the web-browser...

[egolombek]

Go to:

https://docs.google.com/spreadsheets/d/ ... sp=sharing

[mrcoollion]

Ok.. thanks for the url .

I made the search for field and numbers code a bit more robust and cleaned up.

Here is the final version for all to play with.

Regards,

Paul

GoogleFormTest_v001.zip

(2.83 KiB) Downloaded 214 times

[simon.schvartzman]

Hi all, I'm happy to see that everything is moving forward.

@mrcoollion sorry for not answering on time (I'm on a different time zone) but glad to see it has been solved.

@egolombek regarding your idea about having a "generic method" (for sure it would be great) the only way I can think of would be using Google Sheets APIs.

I guess everything you can manually do in the Google Docs environment is doable through APIs (create a Sheet, define columns names, write/read/update cells, etc).

A good place to start would be here https://developers.google.com/sheets/api

Not sure if this would be a valid approach for you. I'd be interested to learn about your progress.

Regards