Microsoft Excel is a powerful tool and is one your business likely already uses regularly. This article will allow you to populate a spreadsheet with MachineMetrics data automatically, rather than manually download new data from app.machinemetrics.com. What we'll cover here is the basics, but it can be extended to do whatever you'd like with your data! For further documentation on our APIs, see our Developer page.
We've also created a pre-built template available for download here if you'd like to jump ahead and get to fetching data. Click the link, then select File>Download>Microsoft Excel.xlsm.
Note* This article is for Windows Excel and will not work on Mac Excel
1. Open a new or existing spreadsheet in Excel
2. If you're already working in a macro-enabled spreadsheet, ignore this step. Otherwise, click the File, Save As, then save as an "Excel Macro-Enabled Workbook" / .xlsm file type
3. Navigate to the Developers tab. If you don't have this tab on your ribbon (top navigation menu), see these instructions for enabling it
4. Click the Visual Basic button, this will open the Visual Basic Editor
5. In the new VB editor window, click the Tools option from the top navigation bar, then select Macros from the dropdown
6. Enter a name for your Macro, then click Create
7. You can write your Macro between "Sub <Your macro's name>" and "End Sub". Or, you can copy and paste our example Macro seen below between those two lines
Sub macroPOST()
Dim URL As String
Dim String1 As String
Dim Json As Object
Dim ia As Integer
StartDate = Format(Worksheets("Input").Range("B3"), "yyyy-mm-dd")
EndDate = Format(Worksheets("Input").Range("B4"), "yyyy-mm-dd")
StartTime = Format(Worksheets("Input").Range("D3"), "hh:mm:ss")
EndTime = Format(Worksheets("Input").Range("D4"), "hh:mm:ss")
URL = "https://api.machinemetrics.com/reports/production"
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "Authorization", "Bearer " & "API KEY HERE"
objHTTP.setRequestHeader "Content-Type", "application/json"
JSONString1 = "{""start"":""" & StartDate & "T" & StartTime & "Z"", ""end"":""" & EndDate & "T" & EndTime & "Z"", ""groupBy"":[{""group"":""machine""},{""group"":""shift""},{""group"":""jobOperation""},{""group"":""operator""},{""group"":""day""}], ""data"":[{""metric"":""rejectedParts""},{""metric"":""scheduledExpectedParts""},{""metric"":""totalParts""},{""metric"":""timeInCycle""}], ""flatten"":""true""}"
objHTTP.Send JSONString1
Worksheets("Data").Activate
Dim Parsed As Dictionary
Sheets("Data").Cells.Clear
Cells(5, 5) = objHTTP.responsetext
Set Parsed = JsonConverter.ParseJson(objHTTP.responsetext)
Dim m As Long
Dim index As Long
Dim longest As Long
Dim currentLength As Long
m = 1
longest = 0
index = 0
For Each Item In Parsed("items")
currentLength = 0
For Each objItem In Item
currentLength = currentLength + 1
Next objItem
If currentLength > longest Then index = m
If currentLength > longest Then longest = currentLength
m = m + 1
Next Item
Set ParsedKeys = Parsed("items")(index)
Dim Key As Variant
Dim KeyList As New Collection
Dim j As Long
j = 0
For Each Item In ParsedKeys.Keys
KeyList.Add Item
Cells(1, j + 1) = Item
j = j + 1
Next Item
Dim i As Long
i = 0
Dim k As Long
For Each Value In Parsed("items")
k = 1
For Each Item In KeyList
Cells(i + 2, k) = Value(KeyList(k))
k = k + 1
Next Item
i = i + 1
Next Value
MakeTable
End Sub
Sub MakeTable()
Dim sht As Worksheet
Dim LastRow As Long
Dim lastCol As Long
Dim StartCell As Range
Dim objTable As ListObject
Set sht = Worksheets("Data")
Set StartCell = Range("A1")
Worksheets("Data").Activate
lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, lastCol).End(xlUp).Row
ActiveSheet.Range("a1:" & _
ActiveSheet.Cells(LastRow, lastCol).Address).Select
Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
objTable.Name = "Data_Import"
End Sub
8. Replace the text 'API KEY HERE' in your macro with your own API key with the reporting scope. If you're unsure of how to do that, see this article on API Keys (You might also notice that this script gets its date range from cells on your sheet. We'll add those in just a minute)
Note: In this example, we've left the sheet name as "Sheet1" but there is no issue with replacing this with a different sheet name as needed
9. Click the Save icon. If you receive a notification saying you aren't working in a macro-enabled spreadsheet, click the Yes button, then follow the instructions for step #2
10. Next, we need to enable a reference. Click the Tools option from the top menu bar then select References from the drop-down. Scroll down until you see "Microsoft Scripting Runtime", check the box to the left, then click OK
11. Add the JsonConverter module from its Github repository. Follow the link, then click the green Code button at the top right, then download the Zip. Extract the contents, then navigate back to your VBA window and click File from the top menu bar and select Import File... Then select the "JsonConverter.bat" file from the download
12. The Macro is finished, but we still need to define the start and end dates for our data request. Close or minimize the VBA window and return to your spreadsheet. Enter your desired start & end dates and times in the following cells and format them in Date format.
Important: Timezones matter! MachineMetrics assumes all dates and times are in UTC format by default. This means you'll need to adjust your hours accordingly or put in an extra line or two of VBA script to compensate. Example: If you were in the EST timezone, you'd need to add 5 hours to your times for them to return the data you're expecting.
B3 - Start Date (ex. 03/21/20)
D3 - Start Time (ex. 12:00:00 AM)
B4 - End Date (ex. 04/21/20)
D4 - End Time (ex. 12:00:00 AM)
13. Select the Developer tab from the navigation ribbon
14. Click the Insert button, then select a button from the Form Control section of the drop-down
15. Click and drag on your sheet where you'd like to create the button
16. From the window that appears, select your Macro, then click OK
17. Left-click your new button to run your Macro! If all goes well, a table will be populated in the "Data" sheet. Take a look at our Developer page for more information about errors
If you're interested in adding more data items to your import, take a look at the "Retrieve production metrics" section of our Aggregates API!
If you have any questions, please reach out to support@machinemetrics.com
Comments
0 comments
Please sign in to leave a comment.