Exporting data from Excel to Google Sheets can be automated with VBA macros. In this post we see how to set up a Google spreadsheet to receive data from Excel through a Google Form. The macro to upload or export Excel data to Google Sheets is fairly simple and is explained in this other post (Upload Data To Google Sheets). This article focuses on the process of adding a Google Form to the Google spreadsheet, adding the fields to the Google Form, and getting the necessary arguments to build the URL link needed to send the server request.
Recipient Google Spreadsheet
We can use an existing Google spreadsheet or add a new one from within Google Sheets or Google Drive. Change the name of the Google spreadsheet as needed. Then we can link a Google Form to the spreadsheet via Tools -> Create a new form.
We can change the name of the form as needed. In this example, the spreadsheet is “Members” and the name of the associated Google Form is “Members Form”. The caption of the form is also “Members Form”.
Then we can add the necessary fields to the form. The form should have as many fields as data columns in the source Excel dataset that we want to export or upload to Google Sheets. The fields should also match the type of data. In this example, we have three fields (name, country, ID), and all of them are of the type “Short answer” in the Google Form.
Now we can get the link of the Google Form in production, i.e. the Google Form accepting user responses. That’s one of the options available when clicking the “Send” button – we need the link to the live form. Navigating the live form, we can then submit a test response (no need to fill the fields). Once submitted, we copy and save the Google Form response URL address, as this is the first part of the URL link to submit the server request from Excel. The Google Form response URL may look like this:
https://docs.google.com/forms/u/0/d/e/1FAIpQLSedSUJHJjPPYQPj2v0ZV5KO-T4lbYTPWD-A8ZQHBueFRfMhyw/formResponse
The second part of the URL link includes the fields or entries IDs along with the data for each field. We can get the entry ID for each field in the form by checking the network components of the POST request generated by Google Forms. We need to open the browser developer console, usually via F12 for most browsers, and then select the “Network” view (it usually shows the “Console” view by default in browsers like Firefox or Chrome). Then we need to look for the POST form response and select that. That shows information about the headers, cookies, request, response, etc, each in a separate tab. In the request tab we can find the entries IDs for each of the fields in the form. That should look like the image below.
Now that we have all the components we can build the URL, which consists of the form response URL concatenated with “&ifq&” along with the entry ID and value of the first field separated with an equal sign, and the same for the second and third fields (or any other fields if more). That would look like the URL link below:
https://docs.google.com/forms/u/0/d/e/1FAIpQLSedSUJHJjPPYQPj2v0ZV5KO-T4lbYTPWD-A8ZQHBueFRfMhyw/formResponse?ifq&entry.957036836=Marcos&entry.402370180=Mexico&entry.884126503=234273483
The value of field1, field2, and field3, corresponds to the name, country, and ID, in this example. We simply get that from the respective cells in columns A, B, and C. We can loop through each row to get the data, and while looping, we send a HTTP server request each time to upload it to Google Sheets.
Excel Data Export Macro
Now that we have the URL to the Google Form linked to the recipient Google spreadsheet, we can use Excel VBA to send a HTTP POST request to that URL. Find below the macro to upload the data from Excel to Google Sheets. It is explained in more detail in this other post (Upload Data To Google Sheets). It simply declares a HTTPreq variable as a new ServerXMLHTTP that is used to send the HTTP server request. We need to add the Microsoft XML 6.0 library to the VBA project.
The macro opens an asynchronous HTTP POST request to the URL link we saw earlier. It also sets the request header to specify the type of content (application/x-www-form-urlencoded; charset=utf-8), and sends the server request.
The data is then 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.
Another way to upload or export, and also import Excel data to or from Google Sheets is using the Google Sheets API. However, that’s more complicated and this simple method saves a lot of struggle to do the job. It saves the time to set a project, API keys, and several other settings. On the other hand, using the Google Form may come with a few limitations. As we have seen, the recipient Google spreadsheet has an additional column for Timestamp. Another disadvantage is that manipulating the data in the Google spreadsheet, for example deleting or adding some rows, could mess up with the form structure. This means, if some rows are deleted in the spreadsheet, the same “responses” should be deleted in the Google Form. So it’s better to not modify the exported data when using this method.
No comments:
Post a Comment