Transfer Data from Microsoft Excel to Google Sheet

Most of the time, we use Microsoft Excel to process and store the data. We always face challenges if multiple users are sitting at different locations and using MS Excel or VBA UserForm to transfer and save the records in a particular database. In multi-user environment, we need a shared drive where we can keep our database or Excel file to get the user’s input in a centralized way. Getting a designated shared drive is costly and not possible in small companies or for individuals. To handle this scenario, we need a free and secure solutions.

Google Drive, Google Sheet and Google Form provide a dynamic solution to store data in a centralized way without worrying about user platform and location. It’s free of cost and fully secure hence, we don’t need to think about data protections and server maintenance.

All the features of Google Drive and Google Sheet raise a question in our mind, Can we use Google Drive or Google Sheet as a database? And the answer is Yes, we can.

In this post, we will learn how to Use Google Drive and Google Sheet to transfer data from MS Excel.

So without any delay, let us start developing this interesting application from scratch.

Develop Excel Based Form to Transfer Data from Excel to Google Sheet

Open a new Excel Workbook and save the file with the name “MS Excel To Google Sheet”. Make sure the extension of this file is .XLSM.

Create a Home page (worksheet) and design a button so that user can click on that button to launch form and submit data. Design the landing sheet as available in below image.

Move to Visual Basic Application Window

Let us move to Visual Basic Application window to design the User Form and write the code to automate the entire process.

To open VBA window, just click on Developer Tab, select Visual Basic icon available in Code group.

Inserting a UserForm and Module

Now, we are in Visual Basic for Applications window. Let us insert a UserForm first.

To insert a blank form, click on Insert menu then select UserForm from menu items.

To write sub procedure to Reset the UserForm and Transfer data to Google sheet, we need to add a Module in our project.

To insert a module, click on Insert menu then select Module from the available menu items.

Adding Microsoft XML, v6.0 Reference

As we need to write the code to transfer data from Excel Sheet to Google Sheet, this macro requires reference to “Microsoft XML, V6.0”.

This particular reference will help us in performing web queries to get and submit data. We will understand the coding thoroughly in later part of our tutorial.

To add the Microsoft XML, v6.0 reference, click on Tools menu then click on References. Find & select it from available references list in Reference window and then click on OK button.

Designing UserForm and Setting Properties

Now, we have done with adding UserForm, Module and XML Reference in this project. Let us design the user interface and set the properties of of TextBox, Option Button and CombBox.

Please refer the below image to design the UserForm and set the properties. We will refer the same name and controls in our coding.

Once you will complete the UserForm designing and properties, it will look like below form in running mode.

Writing VBA code to Reset the form

To write a sub procedure to reset and initialize the user form, double click on Module1 in Project Explorer window and move to code window available in left side.

Write the below Sub Procedure in code window.

Sub Reset()

With frmForm

    .txtEmpID.Value = ""
    .txtEmpName.Value = ""
    .optFemale.Value = False
    .optMale.Value = False
    .cmbDesignation.Clear

    .cmbDesignation.AddItem "Executive"
    .cmbDesignation.AddItem "Sr. Executive"
    .cmbDesignation.AddItem "Team Leader"
    .cmbDesignation.AddItem "Manager"
    .cmbDesignation.AddItem "Sr. Manager"
    .cmbDesignation.AddItem "Associate Director"
    .cmbDesignation.AddItem "Director"

    .txtAddress.Value = ""

End With
End Sub

In this procedure, we are clearing values and selection from input controls. We are also adding the required elements in ComboBox (cmbDesignation).

Assigning Reset() to Form Initialization

Visit to code window of UserForm and write the below code.

Private Sub UserForm_Initialize()
Call Reset
End Sub

Adding code on click event of cmdReset button

Write the below code in form code window.

Private Sub cmdReset_Click()
 Dim i As VbMsgBoxResult

 i = MsgBox("Do you want to reset this form?", vbYesNo + vbQuestion, "Reset")

 If i = vbNo Then Exit Sub

 Call Reset
End Sub

Now, the only one Sub Procedure is pending and that is for Transferring data from Excel to Google Sheet. We will complete this coding after creating a Google Sheet and Form in Google Drive. So, let’s move to the next phase of our development.

Creating Google Sheet and Google Form in Google Drive

Sign in to your Google account.

Click on Google App icon available on the top-right side in window and select the Google Drive.

Creating a Folder in Google Drive

Create a new folder. To do that just click on + icon available on Top-left side in Google Drive and then click on Folder in available menu and then give the folder name as ‘Google Sheet Folder’ and then click on Create button.

Adding a new Google Sheet to the folder

Let us add a new Google Sheet to this folder and give the title as ‘Google Sheet Database’ and rename the ‘Sheet1’ to ‘Data’.

To do that just right click in Folder and select ‘Google Sheet Database’. It will open a blank Google sheet in new tab. Just visit to Google Sheet Tab and change the ‘Untitled Spreadsheet’ to ‘Google Sheet Database’. Also, rename the ‘Sheet1’ to ‘Data’. Now close this tab. Once you close the Google Sheet, you can see a Google Sheet available in folder.

Create a new Google Form in selected folder

Let us create a Google Form in same folder. To insert a Google Form, just right click in Folder then select More from the Pop-up menu, click on Google Forms and then select Blank Form. It will open a blank form with title ‘Untitled form’ in a new tab of your browser.

Change the Form title from ‘Untitled Form’ to ‘Employee Data Entry Form’. Also, add the description as ‘Form to capture employee details’.

Now, we have done with Form title and description. Let us add the questions and required fields in our form. To add questions, just click on + icon available in floating menu. Also, choose the field type from the drop-down available to the right side of question title.

Here, we need to add fields for Employee ID, Employee Name, Gender, Designation and Address. See the below image and follow the steps.

Now, we have done with adding questions and respective fields in Google Form. Let us select the response destination to store input data in Google Sheet.

By default, Google Forms create a Google Sheet to store its data but here, we are taking control in our hand and going to utilize the Google Sheet, which we created i.e. Google Sheet Database.

To select the response destination, click on ‘Responses’ available beside the ‘Questions’ button available on top of Google Form. It will open the ‘Responses’ window. Now, click on ‘More’ icon (three dots) then click on ‘Select response destination’ in pop-up menu. In select response destination window, just click on ‘Select existing spreadsheet’ and the click on ‘Select’, choose the ‘Google Sheet Database’ from the available Google sheets and then click on ‘Select’.

Now, Google Form linked with the Google sheet. Here, Google sheet will store all the responses submitted by either Google Form or Microsoft Excel. It will work like a centralized database to store entire data transferred by users.

So, we have completed the Google Form and Google Sheet. Now, it’s time to get pre-filled link of Google Form so that it can be used in our VBA coding to transfer data from MS Excel to Google Sheet.

Getting Pre-filled Link of Google Form

To get the pre-filled link, just click on more icon available on top-right of Send button. Once you click on more icon (triple dots) then it will open the form in a separate tab. Just fill all the fields with required values, then click on ‘Get Link’ and again click on ‘Copy Link’. It will copy the link of pre-filled form. Just open Notepad and paste the link over there for further use.

Understanding Pre-filled link of Google Form

https://docs.google.com/forms/d/e/1FAIpQLSegAIkkFc1EoIaF5WQIxchEyrVLW3zsjbkP5WDHQB2sIIQK3Q/viewform?usp=pp_url”&entry.837233042=1254&entry.413407046=Dilip+Kumar&entry.1383004734=Male&entry.356904377=Manager&entry.479173200=New+City”

This URL the pre-filled URL which we can use to fill and then submit the data. This URL has two different parts. Let’s break it and understand

First Part of URL

https://docs.google.com/forms/d/e/1FAIpQLSegAIkkFc1EoIaF5WQIxchEyrVLW3zsjbkP5WDHQB2sIIQK3Q/viewform?

This is the first part of the URL, which is to view the google form. Here, we need to make some changes in the first part so that we can utilize in our coding to transfer the data. In this URL, just change viewform? with formResponse?ifq

Here, formRespose has been used to get the response from Google Form after submitting the details through VBA code.

Second Part of URL

“&entry.837233042=1254&entry.413407046=Dilip+Kumar&entry.1383004734=Male&entry.356904377=Manager&entry.479173200=New+City”

Second part of URL contains “name” attributes for the text boxes, option button and combobox used in Google Form. Here, entry.837233042 and entry.413407046 are the name value for two text boxes, Employee ID and Employee Name. Similarly, entry.1383004734 is for Gender, entry.356904377 for designation and entry.479173200 is for Address.

For coding we need to replace the field values (bold text in below link) with respective variables name and then at the end of the link just add &submit=Submit to post all the data to Google Form.

“&entry.837233042=1254&entry.413407046=Dilip+Kumar&entry.1383004734=Male&entry.356904377=Manager&entry.479173200=New+City” & “&submit=Submit”

Now, we understood the technicalities and changes required in URL. Let us write the pending Sub Procedure to transfer the data from MS Excel to Google Sheet.

Write Sub Procedure to send the data to Google Sheet

To understand the purpose of code, I have put comments wherever required. Please go through the code and comments to understand how it works.

Let’s call this Sub Procedure on click event of cmdAdd command Button. To write the code, just move to UserForm code window and write the below code.

Private Sub cmdAdd_Click()

Dim i As VbMsgBoxResult

i = MsgBox("Do you want to transfer the data?", vbYesNo + vbQuestion, "Transfer")

If i = vbNo Then Exit Sub

Call SendToGoogle

End Sub

Sub SendToGoogle()

'This Macro Requires Reference to "Microsoft XML, v6.0" (VBA Editor > Tools > References, find & select from list)

Dim URL_First As String        'Assign the first part of URL to send the data
Dim URL_Last As String         'Assign the last part of URL where we will update the information
Dim Form_URL As String         'To store the Form URL after merging Beginning and End URL

Dim HeaderName As String       'Variable to store the header type i.e. Content-Type
Dim SendID As String           'To store the information required to send a particular information to Google form

'Variables to store user inputs from Excel UserForm 
Dim EmpID As String  
Dim EmpName As String
Dim Gender As String
Dim Designation As String
Dim Address As String


'Assign User inputs to variables

EmpID = frmForm.txtEmpID.Value
EmpName = frmForm.txtEmpName.Value
Gender = IIf(frmForm.optFemale.Value, "Female", "Male")
Designation = frmForm.cmbDesignation.Value
Address = frmForm.txtAddress.Value

'Variable to store what we need to send to server

Dim TicketInfo As MSXML2.ServerXMLHTTP60 'XML variable to send the information to server

'Content-Type is actually a header type which tells the client what the content type of the returned content actually is. Google recognizes this header type

HeaderName = "Content-Type"

'SendID  required to send a particular information to Google Form
SendID = "application/x-www-form-urlencoded; charset=utf-8" 

'In actual link, we need to replace viewform? with formResponse?ifq&
'need to find the “name” attributes for the text boxes and the value for them
'add at the end &submit=Submit and use it, it must post all the data you specified in one step.

'formRespose is used to get the response from Google Form after submitting the details
'Submit - it is a command to submit the filled form

URL_First = "https://docs.google.com/forms/d/e/1FAIpQLSegAIkkFc1EoIaF5WQIxchEyrVLW3zsjbkP5WDHQB2sIIQK3Q/formResponse?ifq"

URL_Last = "&entry.837233042=" & EmpID & "&entry.413407046=" & EmpName & "&entry.1383004734=" & Gender & "&entry.356904377=" & Designation & "&entry.479173200=" & Address & "&submit=Submit"

'Creating the Final URL
Form_URL = URL_First & URL_Last

Set TicketInfo = New ServerXMLHTTP60 'Setting the reference of new server xmlhttp 60

TicketInfo.Open "POST", Form_URL, False ' Posting the entire link

TicketInfo.setRequestHeader HeaderName, SendID 'Specifies the name of an HTTP header.

TicketInfo.send 'Send all the information over google

'StatusText is provide the status of data submission. It will show OK if data will be successfully submitted

If TicketInfo.statusText = "OK" Then 'Check for successful send

  Call Reset 'Call Reset procedure to reset form Excel Form after submitting the data
  MsgBox "Thank you for submitting data!"

Else
  MsgBox "Please check your internet connection & required details"
End If

End Sub

Let’s move to code window of module and write a sub procedure to show the user form.

Sub ShowForm()
 frmForm.Show
End Sub

Assigning Macro on ‘Launch Form’ button available in ‘Home’ worksheet

Now, we have done with all the coding to handle this automation. Let us move to Excel window and assign macro on ‘Launch Form’ button available in ‘Home’ worksheet.

To assign the macro, just right click on ‘Launch Form’ button, select ‘Assign Macro’ and from Assign Macro window, just select the ‘Show Form’ from the available macros in ThisWorkbook macro list and then click on OK.

Testing the Form

So, our application is ready. Let’s open the Form and Google Sheet simultaneously in the same screen to test this automated Data Entry Form.

Here, this application is working perfectly. We can transfer the data from Microsoft Excel to Google Sheet without facing any issues. This tool will work in multi-user environment.

Please watch this complete step by step tutorial on YouTube.

Part 1 – Creating UserForm in Excel and Write code to reset the form

Part 2 – Creating Google Sheets, Google Form, linking Google Form with Google Sheets and generating pre-filled link of Google Form

Part 3 – Understanding Pre-Filled Link of Google Form and Writing Code to Transfer Data from Excel to Google Sheets

Please click on below button to download the Excel file used in this tutorial. Password to open the VBA code is thedatalabs.

Keywords

Automating Google Sheet from Excel

Transferring Data from Microsoft Excel To Google Sheet

How to use Google Drive and Google sheet as a database for Excel Application?

Using cURL commands to transfer data from Excel to Google Sheet

VBA code to transfer data from Excel sheet to Google Sheet

Saving Excel data to Google Sheet automatically

How to create a Google form?

Getting pre-filled link of Google Form