Retrieve info from google sheets - (Moved)

[jrcp]

Hi there, i´m creating a script that allows me to fill in a form online, with data pull out from a google sheet. I have no problems using IE to manipulate the website, but the problem is that i can´t find a way to retrieve information from the google sheets. Do you know if this is possible or any work around would be appreciated, thanks

[AdamUL]

A little bit of googling, and I found GSpread.NET.  I have never used it, so I cannot say how good it is.  You may be able to use it with the Excel UDF, but I'm not certain.

 

Adam

 

[water]

Looks very promising!
Would be interesting to modfiy the Excel UDF (let's say by adding a flag to _Excel_Open) and check how may of the example scripts still work

[Jos]

Moved to the appropriate forum, as the Developer General Discussion forum very clearly states:

Quote

General development and scripting discussions. If it's super geeky and you don't know where to put it - it's probably here.

---

Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums.

Moderation Team

[caramen]

@water Hey mate how are you ? 

Do you did anything on the UDF for GoogleSheet documents ? 

And also doing more research on this subject. 

[water]

Hi caramen,

haven't played with this COM interface yet as I do not have a Google account and do not have the needed spare time at the moment.
But it shouldn't be too hard to replace

Set objExcel = CreateObject("Excel.Application")
with
Set objExcel = CreateObject("GSpreadCOM.Application")

as described on the tools homepage.

 

[caramen]

@water Thanks for your quick reply.

I am gonna try to play with that.

Do you allow me to come in PM to you about this ?

 

Regards.

[water]

Why not discuss it here? So it is useful for other users too. 

[mLipok]

In the first step I was trying to use: GSpreadTest.exe and my primary question are about security.
This testing application asks me in my default browser for access.

How I can be sure that only this single app ... running from my computer will gain access to my GOOGLE account?

 

btw.
quick testing script

_Example()
Func _Example()

    Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
    #forceref $oErrorHandler
    Local $oApp = ObjCreate("GSpreadCOM.Application")
    $oApp.Workbooks.Item("Example2").Worksheets.Item("Sheet1").Activate()

EndFunc   ;==>_Example


; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

 

[water]

My 2 cents worth:

• I would only test with a demo Google account
• As the source of the application is provided you can do a security check
• I would never run a compiled test exe
• The only secure way to connect to Google sheets is to use two-factor-authentification (but I'm not sure this is supported by the application)

Seems you can provide credentials by calling the MailLogon method of Excel or by providing a config file:

Quote

The attentive reader will ask how to provide Google account credentials to make it all work as expected? Well, the good news is that Microsoft added MailLogon method to the Excel. Application interface. So, we use this method for authentication in Google, and, for convenience, we get default credentials from config file.

; $sName - User name, any you like
; $sClientIdAndSecret - "client_id|client_secret" format
; $sScriptId - Google Apps script ID
; https://docs.microsoft.com/en-us/office/vba/api/excel.application.maillogon
Local $oApp = ObjCreate("GSpreadCOM.Application")
$oApp.MailLogon($sName, $sClientIdAndSecret, $sScriptId)

 

Edited May 8, 2019 by water