Integrating Go-UPC Barcode API into Google Sheets
Google Sheets provides a robust and flexible platform for data analysis and collaboration. By integrating APIs with Google Sheets, you can tap into even greater power and functionality. In this tutorial, we will look at how you can integrate the Go-UPC (go-upc.com) barcode API into Google Sheets. This could be a valuable tool to automate the process of searching and retrieving specific information related to UPC barcodes like product names, images, or pricing.
Step 1: Get Go-UPC API Key
You will receive an API key the instant you sign up for any of our plans. If you haven't already, head on over to our account sign-up page and create an account.
Step 2: Copy Our Google Sheets Starter Template
Sign in to your Google account, and then visit our Google Sheets Starter Template . You'll want to select "Make a copy" from the File menu before proceeding..
Step 3: Add Your API Key
Go to the "Settings" tab/sheet of your newly cloned spreadsheet document (by default, it will load the "Products" tab). There is only one property in the settings that will need changed, which is the "API Key" value (cell B2). Just paste your Go-UPC API key in that cell, and then you can return to the "Products" sheet.
Step 4: Open Google Apps Script
Google Apps Script is the platform/interface that Google offers for automating certain tasks, such as this. From the main menu, go to "Extensions" -> "Apps Script". A new tab or window will open where you can input custom JavaScript code that affects the active spreadsheet/document. The interface may be a bit overwhelming at first, but thankfully we'll only be making a few tweaks to get your spreadsheet pulling in barcode data in no time. You won't need to make any changes to this code unless you want to modify it to fit your needs, but we've included script file below for your reference.
Step 5: Configure Triggers
Triggers allow our spreadsheet extension to execute actions automatically when certain events occur. We're going to add an automation for the onEdit event, which will run when a user changes a value in a spreadsheet (specifically, when they add, change, or remove a barcode).
In the Apps Script dashboard, hover over the collapsed menu on the left, and click on "Triggers", then click the "Add Trigger" button in the bottom-right corner. Set each of the drop-down menu fields to the following options and values:
- Choose which function to run :
onEdit - Choose which deployment should run :
Head - Select event source :
From spreadsheet - Select event type :
On edit - Failure notification settings :
Notify me immediately(at least for now, this is our recommendation)
After setting these options, click "Save", and then you will then need to authorize this new application with your Google account. Please note, you will likely see a red warning that says something like "Google hasn't verified this app", at which point you will need to click "Advanced" to expand additional options, and click "Go to Go-UPC JSON API Template (unsafe)". Finally, scroll down and click "Allow", then click "Save" to apply the trigger.
Step 6: Update Project Manifest Settings
Within the Apps Script dashboard, click the "Project Settings" icon in the left menu. Then, under "General Settings", select the "Show 'appsscript.json' manifest file in editor" checkbox.
Now, click on the "Editor" icon in the left menu, and you should see a new file called "appscript.json" in the project files list. Click that to open it, and replace its contents with the code below, and be sure to click the disk/save icon to save all changes to your project:
{
"timeZone": "America/New_York",
"dependencies": {},
"webapp": {
"executeAs": "USER_ACCESSING",
"access": "ANYONE"
},
"exceptionLogging": "STACKDRIVER",
"oauthScopes": [
"https://www.googleapis.com/auth/script.external_request",
"https://www.googleapis.com/auth/spreadsheets.currentonly",
"https://www.googleapis.com/auth/script.scriptapp",
"https://www.googleapis.com/auth/documents.currentonly",
"https://www.googleapis.com/auth/spreadsheets"
],
"runtimeVersion": "V8",
"sheets": {
"macros": [
{ "menuName": "my_getRowValues", "functionName": "my_getRowValues" },
{ "menuName": "my_actionSyncData", "functionName": "my_actionSyncData" },
{ "menuName": "my_getActiveValue", "functionName": "my_getActiveValue" },
{ "menuName": "my_getColumnByName", "functionName": "my_getColumnByName" },
{ "menuName": "my_getObjType", "functionName": "my_getObjType" },
{ "menuName": "my_readNamedRange", "functionName": "my_readNamedRange" },
{ "menuName": "my_initAPIRequest", "functionName": "my_initAPIRequest" },
{ "menuName": "my_getSheetName", "functionName": "my_getSheetName" },
{ "menuName": "my_getSheetID", "functionName": "my_getSheetID" },
{ "menuName": "my_readRows", "functionName": "my_readRows" },
{ "menuName": "my_setActiveValue", "functionName": "my_setActiveValue" },
{ "menuName": "my_staticAPIRequest", "functionName": "my_staticAPIRequest" },
{ "menuName": "my_updateRowWithJSONData", "functionName": "my_updateRowWithJSONData" },
{ "menuName": "onEdit", "functionName": "onEdit" },
{ "menuName": "onOpen", "functionName": "onOpen" }
]
}
}
Step 7: Reload Your Spreadsheet, and Start Adding UPCs!
The only thing you have to do now, is return to the cloned spreadsheet, reload the browser, and start adding barcodes. You should see the data populate within seconds of adding a valid product code. Please don't add multiple codes at a time, as you will likely exceed your short-term request limit.
By following these steps, you can successfully integrate the Go-UPC barcode API into a Google Sheet. Leveraging this Go-UPC API integration can certainly boost productivity and make our services more accessible to those with less technical development experience. We hope you find it useful too!
Google Apps Script Source Code
/**
* Go-UPC Google Sheets API Integration
*
* @author Go-UPC
* @link https://go-upc.com
*/
/**
* CONFIGURATION
*/
// CONSTANTS
var SHEET = my_getSheetName();
var SHEET_TO_EDIT = my_getSheetName();
var SHEET_ID = my_getSheetID();
var COLUMN_NUMBER = 1;
var ROW_START = 7;
// STATIC VARIABLES
const SHEET_DATA = "Products"
const SHEET_SETTINGS = "Settings"
const MIN_CODE_VALUE = 100000
const MAX_CODE_VALUE = 100000000000000
// APP GLOBALS
var API_BASE_URL = "https://go-upc.com/api/v1/code/";
/**
* CUSTOM APPLICATION METHODS
*/
// SETTINGS
var API_KEY = my_readNamedRange("Settings.APIKey");
/**
* CUSTOM APPLICATION METHODS
*/
function my_readNamedRange(name) {
var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(name);
var val = false;
if (range != null && range != undefined) {
val = range.getValues();
}
return val;
}
/**
* Get Current Sheet Name
*
* @return string
*/
function my_getSheetName() {
var val = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
return val;
}
/**
* Get Current Sheet ID
*
* @return string
*/
function my_getSheetID() {
var val = SpreadsheetApp.getActiveSheet().getSheetId();
return val;
}
/**
* Get Object/Value Type
*
* @return string
*/
function my_getObjType(obj) {
var type = typeof (obj);
if (type === "object") {
try {
// Try a dummy method, catch the error
type = obj.getObjTypeXYZZY();
} catch (error) {
// Should be a TypeError - parse the object type from error message
type = error.message.split(" object ")[1].replace('.', '');
}
}
return type;
}
/**
* Get Column Number by Name
*
* @return int
*/
function my_getColumnByName(colName, row) {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var col = data[0].indexOf(colName);
if (col != -1) {
return data[row - 1][col];
}
}
/**
* Retrieves all the rows in the active spreadsheet that contain data and logs the
* values for each row.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function my_readRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
for (var i = ROW_START; i <= numRows - 1; i++) {
var row = values[i];
var data = values[i][0];
Logger.log("ROW DATA :");
Logger.log(row);
Logger.log("COLUMN DATA :");
Logger.log(data);
}
}
/**
* Returns true if the given test value is an object; false otherwise.
*/
function isObject_(test) {
return Object.prototype.toString.call(test) === '[object Object]';
}
/**
* Returns true if the given test value is an array containing at least one object; false otherwise.
*/
function isObjectArray_(test) {
for (var i = 0; i < test.length; i++) {
if (isObject_(test[i])) {
return true;
}
}
return false;
}
/**
* Returns the value in the active cell.
*
* @return {String} The value of the active cell.
*/
function my_getActiveValue() {
var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
return cell.getValue();
}
/**
* Replaces the active cell value with the given value.
*
* @param {Number} value A reference number to replace with.
*/
function my_setActiveValue(value) {
var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
cell.setValue(value);
}
function my_updateRowWithJSONData(dataStr, rowNum) {
Logger.log("RUNNING my_updateRowWithJSONData");
var ss = SpreadsheetApp.getActiveSpreadsheet(),
sheet = ss.getActiveSheet();
var rowContext = ss.getRange("Products!B" + rowNum + ":K" + rowNum);
var dataRows = rowContext.getValues(),
col = 0,
dataVals = dataRows[0];
var codeColumn = ss.getRange("Products!A" + rowNum)
Logger.log("Row Context:")
Logger.log(rowContext)
if (dataStr == '' || dataStr == "{}") {
Logger.log("EMPTY DATA!!!")
for(col = 0; col < dataVals.length; col++) {
dataVals[col] = ''
}
codeColumn.offset(0,0).setBackground("#FFFFFF")
} else {
var productData = JSON.parse(dataStr);
if (productData.product !== undefined && productData.product !== null) {
Logger.log("Product data found");
var codeType = productData.codeType;
var name = productData.product.name;
var desc = productData.product.description;
for(col = 0; col < dataVals.length; col++) {
switch(col) {
case 0:
dataVals[col] = codeType;
break;
case 1:
dataVals[col] = name;
break;
case 2:
dataVals[col] = desc;
break;
case 3:
dataVals[col] = productData.product.region;
break;
case 4:
dataVals[col] = productData.product.imageUrl;
break;
case 5:
dataVals[col] = productData.product.brand;
break;
case 6:
dataVals[col] = productData.product.category;
break;
case 7:
dataVals[col] = productData.barcodeUrl;
break;
case 8:
dataVals[col] = productData.product.upc;
break;
case 9:
dataVals[col] = JSON.stringify(productData.product.specs);
break;
default:
dataVals[col] = '';
}
codeColumn.offset(0,0).setBackground("#FFFFFF")
}
} else {
for(col = 0; col < dataVals.length; col++) {
dataVals[col] = ''
}
dataVals[1] = "(Product Not Found)"
codeColumn.offset(0,0).setBackground("#f6b26b")
}
}
dataRows[0] = dataVals
// Update row with new data
Logger.log("Setting Row Data")
rowContext.setValues(dataRows)
}
function my_getAPIResponse(code) {
var ss = SpreadsheetApp.getActiveSpreadsheet(),
activeSheet = ss.getActiveSheet();
code = (typeof code !== 'undefined') ? code : false;
// rowIndex = (typeof row !== 'undefined') ? rowIndex : activeSheet.getActiveCell();
var gtin = '';
if (code == false) {
gtin = activeSheet.getActiveCell().getValue().toString();
} else {
gtin = code.toString();
}
var endpointURL = API_BASE_URL + gtin + "?key=" + API_KEY;
Logger.log("API Endpoint URL : " + endpointURL);
var response = UrlFetchApp.fetch(endpointURL, { 'muteHttpExceptions': true });
Logger.log("API Response Data : ");
var responseText = response.getContentText();
Logger.log(responseText);
return responseText;
}
function my_initAPIRequest(code) {
code = (typeof code !== 'undefined') ? code : false;
var gtin = '';
var ss = SpreadsheetApp.getActiveSpreadsheet(),
activeSheet = ss.getActiveSheet(),
cell = activeSheet.getActiveCell(),
cellValue = cell.getValue(),
rowIndex = cell.getRow();
if (code == false) {
gtin = cellValue.toString();
} else {
gtin = code.toString();
}
var endpointURL = API_BASE_URL + gtin + "?key=" + API_KEY;
Logger.log("API Endpoint URL : " + endpointURL);
var response = UrlFetchApp.fetch(endpointURL, { 'muteHttpExceptions': true });
Logger.log("API Response Data : ");
var responseText = response.getContentText();
Logger.log(responseText);
my_updateRowWithJSONData(responseText, rowIndex);
}
function my_actionSyncData() {
Logger.log(ScriptApp.getProjectTriggers());
ScriptApp.newTrigger('my_initAPIRequest')
.forSpreadsheet(SHEET_ID)
.onEdit()
.create();
}
function my_staticAPIRequest() {
var gtin = "850483000055"; // code.toString();
var endpointURL = API_BASE_URL + gtin + "?key=" + API_KEY;
Logger.log("API Endpoint URL : " + endpointURL);
var response = UrlFetchApp.fetch(endpointURL, { 'muteHttpExceptions': true });
Logger.log("API Response Data : ");
Logger.log(response.getContentText());
}
/**
* SIMPLE (BUILT-IN) TRIGGERS
*/
/**
* OnOpen
*
* @description - runs automatically when the spreadsheet is opened/loaded
* @link - https://developers.google.com/apps-script/guides/triggers#onopen
* @return void
*/
/**
* OnOpen
*
* @description - runs automatically when the spreadsheet is opened/loaded
* @link - https://developers.google.com/apps-script/guides/triggers#onopen
* @return void
*/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [
{
name: "Sync Data",
functionName: "my_staticAPIRequest"
},
{
name: "Fetch Active",
functionName: "my_initAPIRequest"
},
{
name: "Read Data",
functionName: "my_readRows"
}
];
sheet.addMenu("Go-UPC Tools", entries);
}
/**
* OnEdit
*
* @description - runs automatically when a user changes the value of any cell in a spreadsheet
* @link - https://developers.google.com/apps-script/guides/triggers#onedite
* @return void
*/
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();
var editingSheet = activeSheet;
// Ensure on correct cheet.
if (SHEET == activeSheet.getName()) { // XXX: FIX
var cell = ss.getActiveCell();
var cellNotation = cell.getA1Notation();
var cellValue = cell.getValue();
var rowIndex = activeSheet.getCurrentCell().getRow();
var valType = my_getObjType(cell.getValue());
var activeCell = e.range;
var wsName = activeCell.getSheet().getName();
var cellValues = e.range.getValues();
const sheet = e.range.getSheet();
if (sheet.getName() !== SHEET_DATA) {
Logger.log("INCORRECT")
return;
}
var selection = SpreadsheetApp.getActiveSpreadsheet().getSelection();
var activeRange = selection.getActiveRange();
e.range.getValues().forEach((row, indexRow) => {
const rowNumber = e.range.rowStart + indexRow;
row.forEach((value, indexColumn) => {
var columnIndex = indexColumn + 1
Logger.log("COLUMN EDITED: (ColumnIndex => " + columnIndex + ", RowIndex => " + rowNumber + ", Value => " + value +")")
// Ensure column and row meet our criteria
if (rowNumber >= ROW_START && columnIndex === COLUMN_NUMBER) {
var valType = my_getObjType(value)
Logger.log("Current row number is : " + rowNumber);
Logger.log("Value is a " + valType + " : '" + value + "'");
// Check for empty value
if (value == '' || value == null || value == undefined) {
my_updateRowWithJSONData('', rowNumber)
}
// Check that the value is numeric
if (valType == "number" || parseInt(value) > MIN_CODE_VALUE) {
if (parseInt(value) <= MAX_CODE_VALUE) {
Logger.log("ACTIVE RANGE : " + activeRange);
// Pass the value to the JSON API request method
var dataResponse = my_getAPIResponse(value)
my_updateRowWithJSONData(dataResponse, rowNumber);
}
}
}
});
});
}
}