Google sheets example throws com error

[NassauSky]

Does anyone familiar with working with objects have any idea why it might be throwing COM errors?

Google API doesn't have any samples that make sense to convert to use Google Sheets in Autoit. 
 

_Example()
Func _Example()

    Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
    #forceref $oErrorHandler

;---- From https://scand.com/products/gspread/
   ; $sName - User name, any you like
   ; $sClientIdAndSecret - "client_id|client_secret" format
   ; example client id: 292085223830.apps.googleusercontent.com
   ; $sScriptId - Google Apps script ID
   ; https://docs.microsoft.com/en-us/office/vba/api/excel.application.maillogon
   $sName = "Owner"
   $sClientIdAndSecret = "myclientidString|myclientSecret" ; from generate client_id.json
   $sScriptId = "myappscriptidString"   
   Local $oApp = ObjCreate("GSpreadCOM.Application")
   $oApp.MailLogon($sName, $sClientIdAndSecret, $sScriptId)
   ;$oApp.Workbooks.Item("Example2").Worksheets.Item("Sheet1").Activate()

EndFunc   ;==>_Example

Throws the following errors:
 

GoogleSheetsTest.au3 (16) : ==> COM Error intercepted !
    err.number is:         0x800401F3
    err.windescription:    Invalid class string

    err.description is:     
    err.source is:         
    err.helpfile is:     
    err.helpcontext is:     
    err.lastdllerror is:     0
    err.scriptline is:     16
    err.retcode is:     0x00000000

GoogleSheetsTest.au3 (17) : ==> COM Error intercepted !
    err.number is:         0x000000A9
    err.windescription:    Variable must be of type 'Object'.
    err.description is:     
    err.source is:         
    err.helpfile is:     
    err.helpcontext is:     
    err.lastdllerror is:     0
    err.scriptline is:     17
    err.retcode is:     0x00000000

GoogleSheetsTest.au3 (18) : ==> COM Error intercepted !
    err.number is:         0x000000A9
    err.windescription:    Variable must be of type 'Object'.
    err.description is:     
    err.source is:         
    err.helpfile is:     
    err.helpcontext is:     
    err.lastdllerror is:     0
    err.scriptline is:     18
    err.retcode is:     0x00000000

>Exit code: 0    Time: 0.09846

 

Edited November 4, 2020 by NassauSky
Clarifying my question

[GokAy]

Hey,

I am not familiar with this, however are these strings supposed to be like this? It is as if you are sending them as a literal and not a variable. 

$sClientIdAndSecret = "myclientidString|myclientSecret" ; from generate client_id.json
$sScriptId = "myappscriptidString"

Just looking at them, maybe you need:

Local $sClientIDString = "the actual id here"
Local $smyClientSecret = "copy paste the json contents?"
Local $sScriptID = "actual app script ID" ; Not a string that is = "myappscriptidString"
   
$sClientIdAndSecret = $sClientIDString & "|" & $smyClientSecret ; from generate client_id.json

Again, I am not familiar with this, and may be totally off. Sorry for wasting your time if so

Edited November 3, 2020 by GokAy

[mLipok]

After installing and using test.vbs from instalation package I get this kind of error:

Checking "c:\Program Files\GSpread.NET\GSpreadCOM.tlb"  with @trancexx TLBViewer was also not succesufully for me.

I end with this:

expandcollapse popup

#include <MsgBoxConstants.au3>

;~ https://www.autoitscript.com/forum/topic/204284-google-sheets-example-throws-com-error/
;~ https://scand.com/products/gspread/
#AutoIt3Wrapper_UseX64=n

_Example()

Func _Example()

    Local $oErrorHandler = ObjEvent("AutoIt.Error", _ErrFunc)
    #forceref $oErrorHandler

;---- From https://scand.com/products/gspread/
   ; $sName - User name, any you like
   ; $sClientIdAndSecret - "client_id|client_secret" format
   ; example client id: 292085223830.apps.googleusercontent.com
   ; $sScriptId - Google Apps script ID
   ; https://docs.microsoft.com/en-us/office/vba/api/excel.application.maillogon
   Local $sName = "Owner"
   Local $sClientIdAndSecret = "myclientidString|myclientSecret" ; from generate client_id.json
   Local $sScriptId = "myappscriptidString"
;~    Local $oApp = ObjCreate("GSpreadCOM.Application")
;~    Local $oApp = ObjCreate("{D2AF91CC-092B-3C0A-B60F-5E2EB2C47BB9}")
;~    Local $oApp = ObjCreate("{F0024500-F000-F000-C000-F00000000046}")
;~    Local $oApp = ObjCreate("GSpreadCOM_Application")
   Local $hDLL = DllOpen("c:\Program Files\GSpread.NET\GSpreadCOM.dll" )
   Local $oApp = ObjCreate("{F0024500-F000-F000-C000-F00000000046}", "{8E30FD72-3831-4B04-86D6-F190FDED3785}", $hDLL)
   If @error Then MsgBox($MB_OK + $MB_TOPMOST + $MB_ICONERROR, '', '@error = ' & @error & @CRLF & '@extended = ' & @extended)

   $oApp.MailLogon($sName, $sClientIdAndSecret, $sScriptId)
   ;$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

But with no success.

 

Edited November 3, 2020 by mLipok

[NassauSky]

17 hours ago, GokAy said:

Thanks @GokAy I didn't think it would make a difference but I tried it out anyway.

Thanks @mLipok I tried the exact test.vbs as I was trying to debug the problem but I had the same error message with the vbs code too.