Uploading an Excel spreadsheet to Google Sheets

Question

I've been hitting my head on a brick wall with the following lovely conundrum:

The point is to get data from Excel to Google Sheets without uploading files, but going through the URLs. The following handily explain how all that business transpires:

https://www.youtube.com/watch?v=mX2_XNYPGiI http://ramblings.mcpher.com/Home/excelquirks/exceldocsintegration/excelsheetsv4

I've tried to adapt the following code for the VBA side of things, common to both examples:

Option Explicit

Sub GetDataFromGoogle()

'link to tutorial: https://www.youtube.com/watch?v=mX2_XNYPGiI

 Dim link As String

 link = "https://docs.google.com/spreadsheets/d/e/2PACX-1vSes7Jb06JRy8KSFyNlpUSzNQxSB_HZay4S8AB2IqzpZP0QdwGO5PFSS-6uzd8v_GsjlkXM31pby2jE/pubhtml"
 Sheet4.QueryTables(1).Connection = "URL;" & link
 Sheet4.QueryTables(1).Refresh False
 Sheet4.Columns(1).ColumnWidth = 10

End Sub

Sub PushDataToGoogle()



Dim link As String

link = "https://docs.google.com/forms/d/e/1FAIpQLSeDHEKJmRDynwOAS4g53T9AVMtpXQkWsRGbAzLpLI7rdsbiFA/formResponse?entry.1155739640=4&fvv=1&draftResponse=%5B%5B%5Bnull%2C1155739640%2C%5B%224%22%5D%0D%0A%2C0%5D%0D%0A%5D%0D%0A%2Cnull%2C%2276341394568976993%22%5D%0D"
'go to that link, refresh it and ensure that the first column isn't too narrow

 sheets("Sheet4").QueryTables(1).Connection = "URL;" & link

 sheets("Sheet4").QueryTables(1).Refresh True
 sheets("Sheet4").Columns(1).ColumnWidth = 10

End Sub

On the Google Sheets side, I used a Form to create a spreadsheet that is both the data source for an Excel import and the destination for the export of data. Replcaing POST and GET in the HTML of the form page is the way to get the destination URL. Subsequently, that URL can be either hardcoded or edited with variables to get various inputs for the "entry.######" part of the URL, and then they are passed on to the sheet in the next available line, which would suit my purposes.

I hit a snag(s) with the oAuth2 part of the operation. From the example on the Desktop Liberation site (second link above), credentials are created from Google and then inserted into a code that authenticates the connection before transmitting the data. As such:

Private Function sheetsOnceOff()

    getGoogled "sheets", , _
    "1023445954023-hq8gkdcmo9sue822d23gy9ak5hmun27.apps.googleusercontent.com", _
    "dX7ABCDEGFBETFWtvX5ShmDfrgrQ"

End Function

If that isn't done, the prior lines of code will just return the main Google login page. I've made the credentials, determined the destination key for the spreadsheet, but I get an error in the getGoogled routine that says:

Runtime-error: '-2147024809 (80070057)': The parameter is incorrect."

parameter values are as follows: - scope = 'sheets', as it should be - replacement package = "" - clientID is OK - clientSecret is OK - complain = true - cloneFromScope = "" - apikey =""

I'm thinking the replacement package should not be empty if complain is true, but I feel that I'm out of my depth here. At least the logic of what needs to happen makes sense to me, and I've managed to get the transfer from Google Sheets to work fine, but I'm just not sure how to handle the oAuth2 authentication matter.

Thank you in advance.

Answer 1

The following is by no means a complete solution, but it is a rudimentary start. The condition on the following is to be logged into gmail, and it will move one piece of data into Google sheets with its timestamp. It works by direclty opening the page that appears after a user clicks Submit on the form, thus imitating a submission. Link2 opens the spreadsheet where the response is passed.

The downside is that it requires the user to be logged into gmail already and will open a new tab each time a variable is passed, so avoid modifying it with arrays or loops. However, I will try putting together a prior vba gmail project with this to see if can work. More will be forthcoming, but for a hacked together band-aid solution, this will do if someone needs it.

Credits and info here:

[https://stackoverflow.com/questions/3166265/open-an-html-page-in-default-browser-with-vba https://stackoverflow.com/questions/5915325/open-google-chrome-from-vba-excel

Sub PushDataToGoogle()

Dim chromePath As String

chromePath = """C:\Program Files\Google\Chrome\Application\chrome.exe"""

Dim link As String, link2 As String

link = "https://docs.google.com/forms/d/e/1FAIpQLSeDHEKJmRDynwOAS4gtpXQkWsRGbAzLpLI7rdsbiFA/formResponse?entry.1155739640=4&fvv=1&draftResponse=%5B%5B%5Bnull%2C11557396%224%22%5D%0D%0A%2C0%5D%0D%0A%5D%0D%0A%2Cnull%2C%227634134997568976993%22%5D%0D"
link2 = "https://docs.google.com/spreadsheets/d/1W8A3UuFQTeEwk6-hqERvuIMT_HInySZTNBOIs/edit#gid=11262360"

Shell (chromePath & link)

ThisWorkbook.FollowHyperlink link2

End Sub

Answer 2

Turns out, the solution is considerably simpler, thanks to the Selenium Basic library, available here, with a lot of useful info about how it works:

https://codingislove.com/browser-automation-in-excel-selenium/

A note for your attention: this download needs Selenium GoogleChrome driver version 2.33, the current latest version, to resolve an error with Chrome starting up properly - at least in my case, I need Chrome and not IE working.

In any case, Selenium is a god-send for those in a similar situation. When you download and install Selenium, go into Tools --> References in the VB Editor, and enable the Selenium Type Library.

The following code will do the job:

Option Explicit
Dim myHTML_Element As IHTMLElement
Dim Driver As New WebDriver

Sub seleniumtutorial()

Dim pword As String
Dim link As String, link2 As String

pword = ThisWorkbook.sheets("Sheet1").Range("D10")

Driver.Start "chrome", "https://gmail.com"
Driver.Get ("https://gmail.com")
Driver.FindElementById("identifierId").SendKeys "user@company.com"
Driver.FindElementById("identifierNext").Click
'Driver.Get ("https://sso.diversey.com/nidp/saml2/sso?id=DIVAuthContract30&sid=0&option=credential&sid=0")
Driver.FindElementById("uname").SendKeys "Username"
Driver.FindElementById("pass").SendKeys pword
Driver.FindElementByName("loginButton2").Click

link = "https://docs.google.com/forms/d/e/1FAIpQLSeDHEKJmRDyMt7rdsbiFA/formResponse?entry.1155739640=7&fvv=1&draftR=%5B%5B%5Bnull%2C115B%224%22%5D%0D%0A%2C0%5D%%0A%5D%0D%0A%2Cnull%2C%227634134997568976993%22%5D%0D"
link2 = "https://docs.google.com/spreadsheets/d/1W8A3UuyeEwk6-hqERvuIMT_HInySBOIs/edit#gid=1126962360"

Driver.Get (link)
Driver.Get (link2)

End Sub

link and link2 are, respectively, the page that confirms a response has been submitted and link2 is the link to the spreadsheet that has the list of responses. You can divide this routine into two subs - one logs in the user, and the other creates an array of the items to be fed to google, at which points it just refreshes the same page with the new values, as it cycles through the loop or array.

One more link: Selenium VBA - exit sub without close browser window

Making the variables public ensures that the browser window doesn't close at the end of the session, or you can declare them inside the first sub if you want it to close. As for credentials, you can either hardcode them or feed them from the spreadsheet, but that's a technicality. Beyond that, it seems like a pretty straightforward routine and it does the job really well.

The syntax of the Selenium library is decidedly more modern than VBA and I'd say quite a bit more powerful