Excel > Make (Webhook) > Google Sheets

[Excel_User_10k]

Hi all,

I saw a YT video (

) that explains how to sync Excel with Google Sheets so that when a Cell value is changed, it will update on Google Sheets to do the same. Firstly, I followed the video but it doesn't seem to work for me where a 'test' is done that they type something in to A1, for instance, and the Webhook says something like 'successful' to confirm that it received the update before the tutorial then continues to link it to Google Sheets and then when trying to setup the Google Sheets I don't have the same options as shown in the video when attempting to map it.

This is the code as is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:Z100")) Is Nothing Then
    Dim objHTTP As Object
    Dim URL As String, Json As String, CellValue As String, SheetName As String
    Dim RowNum As Long
    ColLett = Split(Cells(1, Target.Column).Address, "$")(1) 'Column Letter
    RowNum = Target.Row 'Row Number
    SheetName = Name 'Sheet Name
    CellValue = Target.Value 'Cell Value
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = "[URL]?SheetName=" & SheetName & "&Column=" & ColLett & "& RowNumber=" & RowNum & "&CellValue=" & CellValue
    objHTTP.Open "Patch", URL, False
    objHTTP.setRequestHeader "Content-Type", "applicationjson"
    objHTTP.send (Json) 'Send Information

End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

(Where is replaced by the link to the Web...to be able to amend where needed. Thank You.

 

[Dan_W]

The part you will need to replace with the web hook URL is [URL]. Did you set up an account? I think the service is called Make now, and not Integromat.

 

[Excel_User_10k]

Hi Dan,

It appears it has missed out part of what I wrote at the end. Which was actually the more important part -_-. I know everything you have said in your response haha. That is not what I need help with. (I am not going to post the actual Webhook link on a public site so I kept it as the default for demonstration purposes). Firstly, I have tried testing this process shown in the video and it isn't working at the point I mentioned. I am not sure if I am missing something or if it is that as this video is old and Integromat has now changed to Make, that perhaps something has altered that means this does not work anymore?

And then secondly,

What I also said, which it has so rudely missed out, is that the code needs changing as my purpose for this principle are a little different. I want to have a similar setup to this whereby when a new row is added to a table in an Excel file, this will replicate it on a table in Google Sheets, including any changes/row deletions so that the Table in Google Sheets will always be a copy of the Table in Excel. I know that the current coding is not structured to do this but I do not know HOW to change it to fit my purpose. Is this something you can help with please?

Thank You.

 

[Dan_W]

I think the first step is to work out how to sync the sheets. Once you've mastered that, then the particulars of what gets transferred and when can be sorted out.

I've not used Integromat/Make but I know Randy has used it a fair bit in his projects. It might be worth posting a comment on his YouTube channel or on Twitter and asking him what he would advise doing - he'd best placed to know. And Randy has always been very responsive and helpful, so it can't hurt to ask?

Let me know how it goes.