Auto Synchronize Excel Sheets With Google Sheets

How to Automatically Synchronize Excel Sheets With Google Sheets?

Steps:

1. On the Excel Sheet to be Synced add 3 columns (Unique_ID,To_Be_Deleted,Status)

2. Create Google Sheets with same column headers as Excel sheet created in Step 1 (Status and To_Be_Deleted column can be ignored in Google Sheet)

3. Create a Google form having fields corresponding to the Column headers

4. Link the Google Form to the Google Sheet Created in Step 2

5. Generate Prefilled url of the Google Form. Replace /viewform? with /viewResponse?

6. Write VBA code to submit Google form and observe the Form Response Sheet as given below after Step 10

7. Update Vba script for handling edits. (Refer VBA code after Step 10)

8. Update Vba script for handling deletes. (Refer VBA code after Step 10)

9. Create Google App Script for Transferring the final data from Form Response Sheet to Data Sheet

10. Create a trigger that would be triggerring Step 9

'-----VBA Code Starts----'

'--On the VBA editor go to tools/references and select Microsoft XML v3.0

Global deletedFlag

Dim arrRowsToBeDeleted()

Dim intArrSize

Sub syncWithGS()

intArrSize = 0

deletedFlag = False

ThisWorkbook.Sheets("Data").Range("BZ1").Value = "=countA(A:A)"

intTotalRows = ThisWorkbook.Sheets("Data").Range("BZ1").Value

strFname = ""

strLname = ""

strAge = ""

strOccu = ""

strToBeDeleted = ""

strStatus = ""

strUniqueID = ThisWorkbook.Sheets("Data").Range("BA1").Text

Set http = CreateObject("MSXML2.ServerXMLHTTP")

strBaseURL = "https:// ........" ' Link to the google form created

For rowNo = 2 To intTotalRows

strRowUniqueID = ""

strFname = ThisWorkbook.Sheets("Data").Range("A" & rowNo).Text

strLname = ThisWorkbook.Sheets("Data").Range("B" & rowNo).Text

strAge = ThisWorkbook.Sheets("Data").Range("C" & rowNo).Text

strOccu = ThisWorkbook.Sheets("Data").Range("D" & rowNo).Text

strRowUniqueID = ThisWorkbook.Sheets("Data").Range("E" & rowNo).Text

strToBeDeleted = ThisWorkbook.Sheets("Data").Range("F" & rowNo).Text

strStatus = ThisWorkbook.Sheets("Data").Range("G" & rowNo).Text

If strStatus <> "SYNCED" Then

If strRowUniqueID = "" Then

strUniqueID = strUniqueID + 1

ThisWorkbook.Sheets("Data").Range("BA1") = strUniqueID

Else

strUniqueID = strRowUniqueID

End If

strURL = strBaseURL & "&entry.516301360=" & strFname

strURL = strURL & "&entry.543754705=" & strLname

strURL = strURL & "&entry.629895940=" & strAge

strURL = strURL & "&entry.945051393=" & strOccu

strURL = strURL & "&entry.1728486969=" & strUniqueID

strURL = strURL & "&entry.1736578731=" & strToBeDeleted

http.Open "POST", strURL, False

http.send

strResponse = http.statusText

Application.Wait DateAdd("s", 2, Now)

If strResponse = "OK" Then

If strToBeDeleted = "Yes" Then

deletedFlag = True

ReDim Preserve arrRowsToBeDeleted(intArrSize)

arrRowsToBeDeleted(intArrSize) = rowNo

intArrSize = intArrSize + 1

Else

ThisWorkbook.Sheets("Data").Range("G" & rowNo) = "SYNCED"

ThisWorkbook.Sheets("Data").Range("E" & rowNo) = strUniqueID

End If

Call DeleteRows

MsgBox "Done"

End Sub

Function DeleteRows()

If deletedFlag = True Then

For arrRowNo = UBound(arrRowsToBeDeleted) To 0 Step -1

ThisWorkbook.Sheets("Data").Select

ThisWorkbook.Sheets("Data").Rows(arrRowsToBeDeleted(arrRowNo) & ":" & arrRowsToBeDeleted(arrRowNo)).Select

Selection.Delete Shift:=xlUp

End If

deletedFlag = False

End Function

'--VBA code to Erase 'SYNCED' status on data update. This code must be written in VBA of the sheet which is being synchronized with Google sheet

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:F")) Is Nothing Then

colNo = Target.Column

If colNo <> 5 And deletedFlag <> True Then

rowNo = Target.Row

ThisWorkbook.Sheets("Data").Cells(rowNo, 7) = ""

End If

End Sub

'-----VBA Code Ends----'

'-----Google App Script Code Starts----'

var spSheet = SpreadsheetApp.getActiveSpreadsheet();

var dataSheet = spSheet.getSheetByName("Form Responses 2")

var finalSheet = spSheet.getSheetByName("Data")

var lastRowFinalSheet;

var curRowNo;

function syncWithExcel() {

var fname="";

var lname= "";

var occupation = "";

var age = "";

var uniqueID="";

var toBeDeleted = "";

var dataRow=0;

var lastRowRespSheet = dataSheet.getLastRow();

fname = dataSheet.getRange("B" + lastRowRespSheet).getDisplayValue();

lname = dataSheet.getRange("C" + lastRowRespSheet).getDisplayValue();

age = dataSheet.getRange("D" + lastRowRespSheet).getDisplayValue();

occupation = dataSheet.getRange("E" + lastRowRespSheet).getDisplayValue();

uniqueID = dataSheet.getRange("F" + lastRowRespSheet).getDisplayValue();

toBeDeleted = dataSheet.getRange("G" + lastRowRespSheet).getDisplayValue();

lastRowFinalSheet = finalSheet.getLastRow();

var dataPresent = idExists(uniqueID)

if (dataPresent==true) {

dataRow = curRowNo;

if (toBeDeleted == "Yes"){

finalSheet.getRange(dataRow + ":" + dataRow).activate();

finalSheet.deleteRows(spSheet.getActiveRange().getRow(), spSheet.getActiveRange().getNumRows());

}

else

{

finalSheet.getRange("A" + dataRow).setValue(fname)

finalSheet.getRange("B" + dataRow).setValue(lname)

finalSheet.getRange("C" + dataRow).setValue(age)

finalSheet.getRange("D" + dataRow).setValue(occupation)

finalSheet.getRange("E" + dataRow).setValue(uniqueID)

spSheet.refreshAllDataSources();

}

else

{

dataRow = (finalSheet.getLastRow()) + 1;

finalSheet.getRange("A" + dataRow).setValue(fname)

finalSheet.getRange("B" + dataRow).setValue(lname)

finalSheet.getRange("C" + dataRow).setValue(age)

finalSheet.getRange("D" + dataRow).setValue(occupation)

finalSheet.getRange("E" + dataRow).setValue(uniqueID)

spSheet.refreshAllDataSources();

}

function idExists(identifier)

{

curRowNo = 2

var uniqueID = ""

for (var rowNo = 2;rowNo <= lastRowFinalSheet;rowNo++)

{

uniqueID=finalSheet.getRange("E" + rowNo).getDisplayValue();

curRowNo=rowNo

if(identifier==uniqueID){

return true;

}

return false;

}

'-----Google App Script Code Ends----'