Skip to content

Using Library Scripts

Library Scripts provide a way to re-use code across multiple Inspections or Dashboards. This can be code that is internal to your organization, or code from a third-party Library.

In this guide, you will use a Library Script to load the SheetJS Community Edition and read an Excel File which has been uploaded to GS in order to populate Traceability in an Inspection based on the current Part and Machine Traceability.

Prerequisites

This guide assumes that you have:

  • At least one Part.
  • The Traceability Machine, Lot, and Shift. The Machine Traceability will have the preset values Cutting and Threading, and will be set to only accept these values during data entry.

If you do not have these prerequisites, first complete the guides on Collecting SPC Data and Collecting Traceability.

This Guide will utilize two Parts, named 3" Bolt and 5" Bolt".

Uploading the File

To begin, upload an Excel file to GS. The file used in this guide follows the structure of:

Part Machine Lot Shift
3" Bolt Cutting 3X-0026 2
5" Bolt Cutting 3X-0027 1
3" Bolt Threading 3X-0026 3
5" Bolt Threading 3X-0027 3

The exact file used may be downloaded here.

To upload a file to GS:

  1. Navigate to the Files page. An image showing the files page
  2. Press the Upload File button. An image showing the location of the file upload button
  3. Select the Excel file to upload.
  4. Note the ID of the file, which we will use directly in the script. An image showing the files page with the uploaded Excel file in the list and the ID highlighted

Create the Library Script

To create the Library Script which will allow reading Excel files:

  1. Navigate to the list of Library Scripts.
  2. Press the Add button. An image showing the location of the add button in the Library Script list
  3. Fill in the Name with SheetJS.
  4. Press the Save button. An image showing the Library Script create form
  5. Press the Import From URL button. An image showing the import from URL button
  6. Paste the SheetJS URL: https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js
  7. Press the Import button.
  8. Press the Save button. An image showing the code in the Monaco Editor

This allows using the Javascript library SheetJS across multiple Inspections.

Create the Inspection

Next, create an Inspection. For the purpose of this guide, this Inspection will only collect Traceability and the Part.

  1. Navigate to the Inspection list.
  2. Press the Add button. An image showing the location of the Add button on the Inspection list
  3. Fill in the Name field with Read Excel File.
  4. Press the Save button. An image showing the Inspection create form
  5. Add a Part Test.
    1. Set the Script ID to part.
  6. Add a Traceability Test.
    1. Select the Machine Traceability.
    2. Set the Script ID to machine.
  7. Add another Traceability Test.
    1. Select the Lot Traceability.
    2. Set the Script ID to lot.
    3. Disable the test.
  8. Add another Traceability Test.
    1. Select the Shift Traceability.
    2. Set the Script ID to shift
    3. Disable the test. An image showing the Read Excel File Inspection
  9. Select the Save action.

Add the Inspection Script

In order to read the Excel file, you will need to add an Inspection Script:

  1. Select the Scripts action. An image showing the location of the Script button in the Inspection Editor
  2. Add your Library Script.
  3. Add a new Inspection Script. An image showing the location of the script add buttons
  4. Name the Inspection Script Fetch Traceability.

Read the Excel File

When the Inspection starts, we will want to read the Excel file and create a mapping which will allow looking up the Lot and Shift based on the Part and Machine. Start by creating a variable to hold the mapping, and binding to the Inspection onReady event:

let sheetRows;
gsApi.inspection.onReady(async () => {

});

By creating this variable outside of the onReady event, it will be accessible inside of other event functions. This is becuase it was declared outside of the function's closure. The variable will be set inside of the onReady event.

Next, read the Excel file from GS:

gsApi.inspection.onReady(async () => {
    // Get the contents as a Blob
    const blob = await gsApi.file.read({YOUR_FILE_ID});
});

Replacing {YOUR_FILE_ID} with the File ID from Step 4 of Uploading the File. At this point, the contents of the file may be loaded with SheetJS:

// Get the contents as a Blob
const blob = await gsApi.file.read({YOUR_FILE_ID});

// Get the ArrayBuffer, which is expected by SheetJS
const arrayBuffer = await blob.arrayBuffer();

// Create the workbook
const workbook = XLSX.read(arrayBuffer);
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
sheetRows = XLSX.utils.sheet_to_json(worksheet);

However, if you hover over the sheetRows variable, you can see that it is typed to an any. This means that the code editor does not have any information about what type the variable is, and cannot provide autocomplete. To remedy this, at the top of the file add a JSDOC type definition:

/**
 * @typedef {Object} PartScheduleRow
 * @property {string} Part
 * @property {string} Machine
 * @property {string} Lot
 * @property {number} Shift
 */

let sheetRows;
gsApi.inspection.onReady(async () => {

And let the editor know what type it is with an @type annotation:

/** @type {PartScheduleRow[]} */
let sheetRows;

Now, when hovering over sheetRows, you should see autocomplete type information.

Update the Traceability

The Lot and Shift Traceability will need to be updated when the Part or Machine Traceability is changed. To start with, create references to the Sub-Inspection API, the Part API, and the Traceability APIs:

const subInspection = gsApi.inspection.subInspection('subi 1');
const partTest = subInspection.part('part');
const machineTest = subInspection.traceability('machine');
const lotTest = subInspection.traceability('lot');
const shiftTest = subInspection.traceability('shift');

In order to update the Lot and Shift, you will bind to events when the Part or Machine changes. To do this, use the onOptionSelected event:

partTest.onOptionSelected(async () => {
});

machineTest.onOptionSelected(async () => {
});

Tip

If you have set up the Machine Traceability to not force Preset Options, then onOptionSelected will never fire. In this case, use onFocusOut instead.

For now, we will focus on the Part Test. Begin by getting the current properties for the Part and Machine Tests, and make sure that they have been set. If either has not been set, then the function returns early and no further processing takes place:

partTest.onOptionSelected(async () => {
    const partProperties = await partTest.getProperties();
    const machineProperties = await machineTest.getProperties();
    if (!partProperties.part || !machineProperties.value) {
        return;
    }
});

Once you have the values, you may look find the appropriate row from the spreadsheet:

const foundRow = sheetRows.find(row => row.Part === partProperties.part.name && row.Machine === machineProperties.value);
if (foundRow) {

}

Finally, update the Lot and Shift Tests:

if (foundRow) {
    await lotTest.updateProperties({
        value: foundRow.Lot
    });
    await shiftTest.updateProperties({
        value: foundRow.Shift
    });
}

We could be done here, but it would be good to handle Part / Machine combinations that do not exist in the spreadsheet. Modify the previous code to unset the Lot and Shift if the row could not be found:

let lotValue;
let shiftValue;
if (foundRow) {
    lotValue = foundRow.Lot;
    shiftValue = foundRow.Shift;
} else {
    lotValue = null;
    shiftValue = null;
}

await lotTest.updateProperties({
    value: lotValue
});
await shiftTest.updateProperties({
    value: shiftValue
});

At this point, you should still have an empty event for the Machine Test. You could copy and paste the same code into that event, but instead we will abstract it into a function. Start by creating a new function:

async function updateTraceabilityValues() {
}

Next, move the code from the Part Test onOptionSelected event into it:

async function updateTraceabilityValues() {
    const partProperties = await partTest.getProperties();
    const machineProperties = await machineTest.getProperties();
    if (!partProperties.part || !machineProperties.value) {
        return;
    }

    const foundRow = sheetRows.find(row => row.Part === partProperties.part.name && row.Machine === machineProperties.value);

    let lotValue;
    let shiftValue;
    if (foundRow) {
        lotValue = foundRow.Lot;
        shiftValue = foundRow.Shift;
    } else {
        lotValue = null;
        shiftValue = null;
    }

    await lotTest.updateProperties({
        value: lotValue
    });
    await shiftTest.updateProperties({
        value: shiftValue
    });
}

Finally, call the function in both events:

partTest.onOptionSelected(async () => {
    await updateTraceabilityValues();
});

machineTest.onOptionSelected(async () => {
    await updateTraceabilityValues();
});

Testing The Inspection

Navigate to the Inspection and verify that when you select a Part and Machine that the Shift and Lot are filled in automatically. Try selecting a value that does not exist in your Spreadsheet and make sure that Shift and Lot are successfully cleared.

Full Code

/**
 * @typedef {Object} PartScheduleRow
 * @property {string} Part
 * @property {string} Machine
 * @property {string} Lot
 * @property {number} Shift
 */

/** @type {PartScheduleRow[]} */
let sheetRows;
gsApi.inspection.onReady(async () => {
    // Get the contents as a Blob
    const blob = await gsApi.file.read({YOUR_FILE_ID});

    // Get the ArrayBuffer, which is expected by SheetJS
    const arrayBuffer = await blob.arrayBuffer();

    // Create the workbook
    const workbook = XLSX.read(arrayBuffer);
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    sheetRows = XLSX.utils.sheet_to_json(worksheet);
});

const subInspection = gsApi.inspection.subInspection('subi 1');
const partTest = subInspection.part('part');
const machineTest = subInspection.traceability('machine');
const lotTest = subInspection.traceability('lot');
const shiftTest = subInspection.traceability('shift');

async function updateTraceabilityValues() {
    const partProperties = await partTest.getProperties();
    const machineProperties = await machineTest.getProperties();
    if (!partProperties.part || !machineProperties.value) {
        return;
    }

    const foundRow = sheetRows.find(row => row.Part === partProperties.part.name && row.Machine === machineProperties.value);

    let lotValue;
    let shiftValue;
    if (foundRow) {
        lotValue = foundRow.Lot;
        shiftValue = foundRow.Shift;
    } else {
        lotValue = null;
        shiftValue = null;
    }

    await lotTest.updateProperties({
        value: lotValue
    });
    await shiftTest.updateProperties({
        value: shiftValue
    });
}

partTest.onOptionSelected(async () => {
    await updateTraceabilityValues();
});

machineTest.onOptionSelected(async () => {
    await updateTraceabilityValues();
});

See Also