Bulk updating device info using a CSV export from a third party asset management system

Many districts use 3rd party asset management database software and/or barcoding systems to associate Asset IDs and metadata such as Serial Number, Location, Notes, Assigned User, Repair Status, etc.  Typically these systems offer the ability to export tabular asset data as a CSV file.   Using this CSV file, and some basic spreadsheet formulas, Chrome Gopher makes it easy to keep your 3rd party asset management software in sync with data in the Chrome management console, without any painful manual data entry.

Requirements for asset data export: CSV export from asset management system must include Serial Number.  This is a very common field to keep in an asset tracking system, and can be scanned via barcode from most devices.

Example CSV Export from 3rd party asset system:

Steps to update:

  1. Use Chrome Gopher to Import devices to Sheet and be sure your import includes all relevant devices that you want to bulk update with your asset system’s downloaded CSV data.
  2. From the Sheet, navigate to File -> Import…-> Upload and select the relevant CSV file from your computer, and use the Import action Insert new sheet(s) to bring the data into it’s own Sheet.
  3. To make it easier follow this example, including exact formulas, rename the tab that contains the imported data “CSVImport.” (no spaces)
  4. In the Devices tab, use the combination of INDEX and MATCH formulas to perform a lookup against serial number in the CSVImport tab and populate the relevant data from your CSV import.  Use copy/paste to extend formulas to relevant ranges.
  5. Once all formulas have been applied, copy (ctrl-C) and paste the data (right click “Past values only”) as values into the Devices tab.
  6. From the Bulk update from Devices tab run the update.

Using INDEX and MATCH formulas to perform lookups

Formula Inputs What it does
match(search_key, range, [searchtype]) search_key: value or reference that you want to find a match for

range: vertical range in which you want to look for a matching value

searchtype: -1, 0, or 1 -- Important: 0 is “exact match” and is almost always what you should use.
Returns the row number of a matching record. 
 index(reference, [row], [column]) reference: rectangular range you want to pull a value from

row: row within the reference range you want to pull data from

column: column within the reference range you want to pull data from
Returns a single value from a defined row, column coordinate location within a rectangular range. 

Tip:  When making cell references, using a $ prefix before a column or row reference will allow that reference to remain fixed when copying / pasting formulas into new rows / columns.

Combining these two into a single formula index(match()), we can now populate Annotated User, Annotated Location, Annotated Asset ID, and Notes columns in the Devices tab.

 

Example 1:

Reading this formula like a sentence: “Go into the rectangle of data defined by ‘CSVImport!$A:$E’ and return the value from the row number in the range ‘CSVImport!$A:$A’ that matches the value in cell $F2 in the devices tab, in column 4 (the assigned_user column) of that row.

 

Example 2:

Reading this formula like a sentence: “Go into the rectangle of data defined by ‘CSVImport!$A:$E’ and return the value from the row number in the range ‘CSVImport!$A:$A’ that matches the value in cell $F2 in the devices tab, in column 3 (the room column) of that row.