Upload Data To Google Sheets Excel VBA Macro

In this post we learn how to upload or export data from Excel to Google Sheets using VBA macros. The macro sends a HTTP request to POST the data through a Google Form into the Google spreadsheet. For that reason, we need to add the Microsoft XML 6.0 library to the VBA project. Another thing we need to do is to link the recipient Google spreadsheet to a Google Form, which needs to have as many fields as data columns to be exported from Excel. Additional details about the Google Form response URL can be found in this other article (Excel Data Export To Google Sheets).

Macro/VBA code:

 

  Sub UploadToGoogleSheets()

      Dim r As Integer, key As String, url As String

      Dim field1 As String, field2 As String, field3 As String

      Dim HTTPreq As New MSXML2.ServerXMLHTTP

 

      For r = 2 To 4 'replace 4 with last row with data

 

      field1 = Range("A" & r).Value

      field2 = Range("B" & r).Value

      field3 = Range("C" & r).Value

       

      key = "1FAIpQLSedSUJHJjPPYQPj2v0ZV5KO-T4lbYTPWD-A8ZQHBueFRfMhyw"

      url = "https://docs.google.com/forms/u/0/d/e/" & key & _

      "/formResponse?ifq" & _

      "&entry.957036836=" & field1 & _

      "&entry.402370180=" & field2 & _

      "&entry.884126503=" & field3

 

      With HTTPreq

          .Open "POST", url, False

          .setRequestHeader "Content-Type", _

          "application/x-www-form-urlencoded; charset=utf-8"

          .send

      End With

 

      Next r

  End Sub

 

 

Macro explained:

• We need to declare a string variable to store the URL – see later how to build the url. We also need to declare a variable as a new ServerXMLHTTP that is used to send the HTTP request. IMPORTANT: We need to add the Microsoft XML 6.0 library to the VBA project from Tools->References in the Visual Basic editor. We shall also declare as many variables as fields in the data set.
• We need to have a recipient Google spreadsheet linked to a Google Form. Then we need to get the Google Form URL and concatenate with “&ifq&” along with each of the entries IDs and the entry value from the source data separated with the “equal” sign. See this other article (Excel Data Export To Google Sheets) to see how to create a Google spreadsheet linked to a Google Form and find the Google Form response url and entries IDs.
• In the example above, there are three fields (name, country, and ID). We put that into the variables field1, field2, and field3 for each row. A For loop is used to loop through the rows in the data set.
• The URL is built with the Google Form response link (the URL after submitting a response). The Google Form response URL may look like this:

 

https://docs.google.com/forms/u/0/d/e/1FAIpQLSedSUJHJjPPYQPj2v0ZV5KO-T4lbYTPWD-A8ZQHBueFRfMhyw/formResponse

 

• Then we need to concatenate it with “&ifq&” followed by the entries as explained earlier. The final URL including the entries IDs and data for the three fields in this example may look like this:
   

https://docs.google.com/forms/u/0/d/e/1FAIpQLSedSUJHJjPPYQPj2v0ZV5KO-T4lbYTPWD-A8ZQHBueFRfMhyw/formResponse?ifq&entry.957036836=Marcos&entry.402370180=Mexico&entry.884126503=234273483

 

• Then we open a HTTP POST request to that URL (False indicates the request is asynchronous). We also need to set the request header to specify the type of content (application/x-www-form-urlencoded; charset=utf-8). Finally, we send the server request.
• The data (the three fields in this example) is exported or uploaded to the recipient Google spreadsheet through the linked Google Form. Google Sheets adds a Timestamp column when linked to a Google Form. That column cannot be deleted, but it can be hidden. As that’s in column A, using this method creates an offset of one column with respect of the source data in Excel.

This is how we upload Excel data to Google Sheets with VBA macros.

Other examples:

• How to Consolidate Filtered Data From Multiple Sheets with Excel VBA macros
  
• How to Combine All Workbooks In Folder with Excel VBA macros
  
• How to Copy Columns To Separate Sheets with Excel VBA macros