Use Google Scripts & Sheets with our APIs

This article will walk you through using both Google Sheets and Google Scripts to create an automated import of MachineMetrics data. Both apps are free and only require you to have a Google Account. If you want to learn more about using Google Scripts, please take a look at Google's developer documentation.

1. Create a new, or open an existing Google Sheet

2. Rename your first sheet "ExampleData". This will be important for the script we're going to be using

3. Add another sheet using the + icon in the bottom left of the window and name it "Charts".

4. From the top navigation bar, click the Extensions option and select Apps Script from the dropdown. This will open a new window in the Apps Scripts project editor.

5. Rename your project by clicking Untitled project in the upper left of the window, then entering a new name

6. Within the Code.gs window, you could write your own script to pull data from MachineMetrics. However, this article will be assuming that you're using the code below. Simply replace everything within the Code.gs window with the pre-written function below.

function getData() {
let chartSheetName = "Charts"
let dataSheetName = "ExampleData"

let apiKey = PropertiesService.getScriptProperties().getProperty('APIKey');
const url = "https://api.machinemetrics.com/reports/production";

let chartsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(chartSheetName);

//Getting API request's date range from cells on the sheet to allow for easy changes
let startDateInput = chartsSheet.getRange("D2").getValue();
let endDateInput = chartsSheet.getRange("F2").getValue();

let startDate = Utilities.formatDate(startDateInput, "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'")
let endDate = Utilities.formatDate(endDateInput, "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'")

// The API key is set in this project's properties for security


// To fetch additional data or configure how it is returned by this request, see developers.machinemetrics.com then modify the body variable
let body =
{"start": startDate,
"end": endDate,
"data": [
{
"metric": "downtime"
},{
"metric": "oee"
},{
"metric": "goodParts"
},{
"metric": "actualPartTime"
},{
"metric": "timeInCut"
}],
"groupBy": [{
"group": "machine"
}],
"flatten": true}

let options = {
"method": "post",
"contentType":"application/json",
"headers": {
"Authorization": "Bearer " + apiKey
},
"payload": JSON.stringify(body)

};
let response = UrlFetchApp.fetch(url, options);
let data = JSON.parse(response.getContentText());
let keys = Object.keys(data.items[0]);
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheetName)

//remove old data
sheet.clear({contentsOnly: true});

//create column headers for each data item
keys.map(function (item){
sheet.getRange(1, sheet.getLastColumn()+1).setValue(item);
});

//Sets data values
for (let i = 0; i < data.items.length; i++){
let item = data.items[i];
let j = 0;
while (j < keys.length){
sheet.getRange(i+2,j+1).setValue(item[keys[j]])
j++
}
}
}

7. There is still one more thing we need to do in the Google Scripts editor, which is to give our script an API key with the scope of reporting. Otherwise, our script won't have permission to access your data. If you're unsure of how to generate an API key, see this article on our API Keys

Important: This step requires using the classic Google Script editor. To switch to the classic script editor, click "Use classic editor" button at the top-right while editing a script.

Once you have your API key, from the top navigation bar, click File and select Project properties

8. Click the Project Settings icon in the left navigation bar, scroll down to the Script Properties section, then click Add script property. Give your new Property the name of "APIKey" the API key you generated as a value. Click Save script properties.

9. Click the Save button once you've finished. Then click the Save icon to finish your script! However, we're not quite ready to run it.

10. Navigate back to your spreadsheet. On your "Charts" sheet, add a start date in cell D2 and an end date in cell F2. The dates should be in yyyy-MM-dd format. Labeling them isn't required for the script to function, but is far easier for future use.

11. Next, we'll create an easy way to run our script. This could be done in a number of ways, but in this example, we'll create a button.

From the top navigation bar, click Insert, then select Drawing from the drop-down

12. In the Drawing window, click on the Shape icon, hover over Shapes, then select the shape you'd like to use for your button. I selected the "Beveled" shape

13. The rest of the button's design is up to you! Once you're satisfied, click the Save and Close button at the top right

14. Click on your new button, then on the three vertical dots that appear at the top right of the button. Then click Assign script.

15. In the window that appears, enter the name of your script function. If you're using the code we provided above, enter "getData" into the text box. Click the OK button once you've finished

16. Click on your button! Before it can run, you'll need to authorize your script. Follow the steps as prompted. Once you've finished, you'll need to click your button one more time.

17. The query will take only a few seconds to run, although if you selected a large date range or have a large number of machines, it might take longer. You'll see this message once it has finished.

18. Switch to your ExampleData sheet to see if your data populated. If it did not, double-check the previous steps or contact support@machinemetrics.com. It should look similar to the data shown in the image below

19. From here on out the data is yours to do whatever you'd like with! What you do with it is up to you. I went ahead and created a pair of charts on the "Charts" sheet in order to better visualize the data. One for OEE, and another for In Cut Hours.

For more info on how to use Google Sheets, take a look at Google's Help Center

If you like to experiment with other metrics, click here to our API Docs to learn how to access other metrics you may want to report on.

Anytime we want to update this data, we can change the start or end dates and then click the button we assigned our script to. The charts and any other formulas you have tied to that data will update automatically once the new data is loaded.

For any questions, please reach out to your CSM or support@machinemetrics.com