asked on

Excel VBA Rest API

Currently I pull Goformz data using Excel VBA code which looks like the code below which works fine.

' Get Rest API Data from Goformz
Dim strUserName As String
Dim strPassword As String

strUserName = ""
strPassword = ""

Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "https://api.goformz.com/v2/formz?" & "status=draft" & "&filter=lastupdateddate%20gt%20" & Sheets("Values").Range("B7") & "%20and%20lastupdateddate%20lt%20" & Sheets("Values").Range("B8") & "&pagesize=" & Sheets("Draft Forms").DraftFormsComboBox1.value & "&pagenumber=" & Sheets("Values").Range("PageNumber").value & "&sort=lastupdateddate%20desc", False
http.setRequestHeader "Authorization", "Basic " & Base64Encode(strUserName & ":" & strPassword)
http.send
Set JSON = ParseJson(http.responseText)
i = 5
For Each Item In JSON
Sheets("Draft Forms").Cells(i, 1).value = Item("status")("status")
Sheets("Draft Forms").Cells(i, 2).value = Format(DateAdd("h", 11, Replace(Left(Item("lastUpdateDate"), 19), "T", " ")), "dd/mm/yyyy hh:mm:ssam/pm")
Sheets("Draft Forms").Cells(i, 3).value = Item("templateId")
Sheets("Draft Forms").Cells(i, 4).value = Item("templateUrl")
Sheets("Draft Forms").Cells(i, 5).value = Item("name")
Sheets("Draft Forms").Cells(i, 6).value = Item("url")
Sheets("Draft Forms").Cells(i, 7).value = Item("assignment")("url")
i = i + 1
Next
     
Sheets("Draft Forms").Range("A4").value = "Status"
Sheets("Draft Forms").Range("B4").value = "Date"
Sheets("Draft Forms").Range("C4").value = "TemplateID"
Sheets("Draft Forms").Range("D4").value = "TemplateURL"
Sheets("Draft Forms").Range("E4").value = "SIte Name"
Sheets("Draft Forms").Range("F4").value = "FormURL"
Sheets("Draft Forms").Range("G4").value = "UserURL"
Sheets("Draft Forms").Range("H4").value = "FirstName"
Sheets("Draft Forms").Range("I4").value = "LastName"
Sheets("Draft Forms").Range("J4").value = "UserName"
Sheets("Draft Forms").Range("K4").value = "Template"

Open in new window

BUT i seem to be having trouble trying to work out what the field names are for another URL that outputs the JSON data as per attachment form.txt.

e.g. if i wanted to pull this information and put in excel sheet named formdata, what are the actual fields names i use in my excel vba code.

If you look at the VBA code i have above to get the status value from the JSON output i would enter the following code

Sheets("Draft Forms").Cells(i, 1).value = Item("status")("status")

Open in new window

Here is the information that i want to be able to pull from the output that i have copied into a txt file.

"formId": "3ed4424d-50b6-490c-9101-811d17690fed"
"name": "FJ7443 - 146943 - 7 ELEVEN ALTONA WEST "
"status": "complete"
"changeDate": "2017-10-23T04:38:46.0000000+00:00"
"lastUpdateDate": "2017-10-23T04:38:48.0000000+00:00"
"url": "https://api.goformz.com/v2/users/8721d153-94fc-4376-8e6a-a5170073ef64"
"location": {
"latitude": -38.038005329209859,
"longitude": 145.32270499717288,
Form.txt

Rgonzo1971

Hi,

pls try

For Each Item In JSON
Sheets("Draft Forms").Cells(i, 1).value = Item("formId")
Sheets("Draft Forms").Cells(i, 2).value = Item("name")
Sheets("Draft Forms").Cells(i, 3).value = Item("status")("status")
Sheets("Draft Forms").Cells(i, 4).value = Format(DateAdd("h", 11, Replace(Left(Item("status")("changeDate"), 19), "T", " ")), , "dd/mm/yyyy hh:mm:ssam/pm")
Sheets("Draft Forms").Cells(i, 5).value = Format(DateAdd("h", 11, Replace(Left(Item("lastUpdateDate"), 19), "T", " ")), , "dd/mm/yyyy hh:mm:ssam/pm")
Sheets("Draft Forms").Cells(i, 6).value = Item("assignment")("url")
Sheets("Draft Forms").Cells(i, 7).value = Item("location")("longitude")
Sheets("Draft Forms").Cells(i, 8).value = Item("location")("latitude")
i = i + 1
Next

Open in new window

Regards

Troy

ASKER

Hi Rgonzo1971,

Thanks for the response but perhaps i wasn't clear enough.

I am not trying to pull the values from the "Draft Forms" sheet but from the results of the query string that i am running in my Excel VBA Code.

Query String looks like this:

https://api.goformz.com/v2/formz/c416112b-9830-41a5-a5b4-04c1a9449972

Excel VBA code to get data using a api url looks like this:

' Get Rest API Data from Goformz
Dim strUserName As String
Dim strPassword As String

strUserName = ""
strPassword = ""

Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "https://api.goformz.com/v2/formz/c416112b-9830-41a5-a5b4-04c1a9449972", False
http.setRequestHeader "Authorization", "Basic " & Base64Encode(strUserName & ":" & strPassword)
http.send
Set JSON = ParseJson(http.responseText)
i = 5
For Each Item In JSON

Open in new window

I'm struggling to understand what the actual fields names are that i put after the "For Each Item in JSON...." code.

e.g. I want to be able to place formid, name, status, text from site address and text from ecl group reference values into cells located in sheet "FormData"

Part of the output looks like this (the whole output is in the forms.txt file i attached earlier)

{
  "formId": "3ed4424d-50b6-490c-9101-811d17690fed",
  "name": "FJ7443 - 146943 - 7 ELEVEN ALTONA WEST ",
  "overrideDefaultFormName": false,
  "status": {
    "status": "complete",
    "changeDate": "2017-10-23T04:38:46.0000000+00:00"
  },
  "lastUpdateDate": "2017-10-23T04:38:48.0000000+00:00",
  "assignment": {
    "id": "8721d153-94fc-4376-8e6a-a5170073ef64",
    "type": "User",
    "url": "https://api.goformz.com/v2/users/8721d153-94fc-4376-8e6a-a5170073ef64"
  },
  "location": {
    "latitude": -38.03800532920986,
    "longitude": 145.32270499717288,
    "accuracy": null
  },
  "templateId": "7ab78594-4694-4d03-8242-a472003e0cef",
  "templateUrl": "https://api.goformz.com/v2/templates/7ab78594-4694-4d03-8242-a472003e0cef",
  "fields": {
    "Job Status - Service Docket": {
      "checkedItems": [
        "Incomplete",
        "Job Completed"
      ],
      "id": "fb438725-e2d4-48a4-9845-a3b700879f7e",
      "name": "Job Status - Service Docket",
      "type": "CheckBoxGroup"
    },
    "Customer Reference #": {
      "text": "NA",
      "id": "09a948b0-335b-47c0-b518-a3bc0179c2dc",
      "name": "Customer Reference #",
      "type": "TextBox"
    },
    "ECL Group Reference": {
      "text": "FJ7443",
      "id": "c4935e9c-77dd-4674-a578-a3bd004e329d",
      "name": "ECL Group Reference",
      "type": "TextBox"
    },
    "Site Address": {
      "text": "Maidstone st",
      "id": "c4f5e0eb-3182-431e-a0ac-a3bd004e32c2",
      "name": "Site Address",
      "type": "TextBox"
    },
    "Reported Fault": {
      "text": "Fit dispensers ",
      "id": "025b5d48-41ad-44be-8da2-a3c600595792",
      "name": "Reported Fault",
      "type": "TextBox"
    },
    "Site Name": {
      "text": "7 ELEVEN ALTONA WEST ",
      "id": "94dc995b-9934-45a5-baca-a3ca005277dd",
      "name": "Site Name",
      "type": "TextBox"
    },
    "ECL Group Employee": {
      "text": "Wail Shweta",
      "id": "54df5e02-c998-4619-9a36-a3ca0060aee2",
      "name": "ECL Group Employee",
      "type": "TextBox"
    },
    "ECL Group Employee  Sign Off - WPCG and Site": {
      "location": {
        "latitude": -37.85745760882484,
        "longitude": 144.80960237197803,
        "accuracy": null
      },

Open in new window

Troy

ASKER

I am pretty close to figuring this out. All i need now is the code i put in for the rowset

The header information comes across fine. I am just not sure what to put in for the rowset code

Set rowSet = JSON("What do i put in here for the row data")

Open in new window

Code i have so far:

Public Sub ImportGoformz()

Dim strUserName As String
Dim strPassword As String
Dim strResponseReceived As String
 
strUserName = ""
strPassword = ""
  
Dim http As Object, JSON As Object, i As Integer, headers As Object, rowSet As Object
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "https://api.goformz.com/v2/formz/c416112b-9830-41a5-a5b4-04c1a9449972", False
http.send
Set JSON = ParseJson(http.responseText)
Set headers = JSON("fields")
Set rowSet = JSON("What do i put in here for the row data")
j = 1
For Each Header In headers
Sheets("FormData").Cells(1, j).value = Header
j = j + 1
Next
i = 2
For Each Item In rowSet
j = 1
For Each detail In Item
Sheets("FormData").Cells(i, j).value = detail
j = j + 1
Next
i = i + 1
Next
MsgBox ("complete")
End Sub

Open in new window

Rgonzo1971

Sorry can't help further

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.