Resources

DeviceAtlas Google Sheets Integration

Resources

The DeviceAtlas Cloud Service can be used within Google Sheets by integrating a custom function: simply follow the steps below to automatically convert a list of UA strings in a Google Sheet into detailed device information. If you do not have a licence please obtain one here.

DeviceAtlas Cloud Client custom function

/**
 * @Customfunction
 */
function deviceatlasCloudAPI(user_agent, header_row, licencekey) {
  if (!user_agent) {
    return;
  }
  if (!header_row) {
    throw 'Error. The header_row parameter must not be empty.';
  }
  if (!licencekey) {
    throw 'Error. The licencekey parameter must not be empty.';
  }

  var documentProperties = PropertiesService.getDocumentProperties();
  var jsonResponse = documentProperties.getProperty(user_agent)

  if (!jsonResponse) {
    var url = 'https://region0.deviceatlascloud.com/v1/detect/properties?licencekey=' +
      licencekey + '&useragent=' + encodeURIComponent(user_agent);
    jsonResponse = UrlFetchApp.fetch(url).getContentText();

    documentProperties.setProperty(user_agent, jsonResponse);
  }

  var props = JSON.parse(jsonResponse);

  if (!props) {
    throw 'Error. Could not perform DeviceAtlas Cloud lookup.';
  }

  props = props.properties;

  var header = SpreadsheetApp.getActiveSheet().getRange(header_row).getValues()
  var result = [];

  header[0].map(propName => {
    result.push(typeof(props[propName]) !== "undefined" ? props[propName] : null);
  });

  return [result];
}

1. Install the DeviceAtlas Cloud custom function

  1. Open a spreadsheet in Google Sheets
  2. Click Tools.
  3. Click Script Editor.
  4. Copy and paste the DeviceAtlas Client custom function in the editor area.
  5. Click Save project.

2. Set up the Google Sheet

  1. Create a column for the User-Agents.
  2. Create columns for the DeviceAtlas properties that you are interested in (use the value in the "API Name" column).

3. Use the DeviceAtlas Cloud Client

The DeviceAtlas Cloud Client can be used like any of the built-in functions like AVERAGE, SUM, and VLOOKUP. The DeviceAtlas Cloud Client custom function is defined as follows:

deviceatlasCloudAPI(user_agent, header_row, licencekey)

  • The user_agent parameter is the row cell that contains User-Agent value to be looked up.
  • The header_row parameter is a range of cells containing the DeviceAtlas properties.
  • The licencekey parameter is your Cloud Service licence key.