DevBlocks Agency
DevBlocks Agency
Zapier - Connect MySQL to Smartsheet (Advanced)
🏎

Zapier - Connect MySQL to Smartsheet (Advanced)

A zap code step was created to whenever a row was created/updated, this code would check if the code is found in smartsheet, and return the row id to update the information in the row.

If Zapier doesn´t find the column, then it would return 0 value, as this would indicate we will need to add a new row.

This code step was configurable with the next field data:

  • productCode
  • sheetId
  • token

Code to extract information 

let uri = "https://api.smartsheet.com/2.0/search/sheets/";
let sheetId = inputData.sheetId;
let productCode = inputData.productCode;

let options = {
  method: 'GET',
  headers: {'Authorization': 'Bearer '+inputData.token}
};

productCode = parseInt(productCode,10);
fetch(uri+sheetId+'?query="ID'+productCode+'"', options)
  .then(function(res) {
    return res.json();
  }).then(function(json) {
      if(json.totalCount == 0){
        callback(null, {"rowId":0});
      }else{
        let array = json.results;
        let rowId = 0;
        for (let index = 0; index < array.length; index++) {
            if(array[index].objectType = "row"){
                rowId = array[index].objectId
                break;
            }            
        }
        callback(null, {"rowId":rowId});
      }
  });

We find in the project that it was better to add the row if we didn´t find one, as we save one task execution on Zapier, by doing it directly on the code, and the user would still have the power to update the row in the next step.

This code step was configurable with the next field data:

  • productCode
  • sheetId
  • token
  • columnId

Code with update/new Row 

let sheetId = inputData.sheetId;
let uri = "https://api.smartsheet.com/2.0/search/sheets/" + sheetId;
let uriToAdd = "https://api.smartsheet.com/2.0/sheets/" + sheetId + "/rows";

let productCode = inputData.productCode;
productCode = parseInt(productCode, 10);

let options = {
  method: 'GET',
  headers: { 'Authorization': 'Bearer ' + inputData.token }
};


let cellOptions = {
  "cells": [
    {
      "value": productCode,
      "columnId": inputData.columnId
    }
  ]
}

let optionsToAdd = {
  method: 'POST',
  body: JSON.stringify(cellOptions),
  headers: {
    'Authorization': 'Bearer ' + inputData.token,
    'Content-Type': 'application/json'
  }
};

function AddRow(uriToAdd, optionsToAdd) {
  fetch(uriToAdd, optionsToAdd)
    .then(function (resAdd) {
      return resAdd.json();
    }).then(function (jsonAdd) {
      if (jsonAdd.message == "SUCCESS")
        callback(null, { "rowId": jsonAdd.result.id, "method": "new" });
      else
        throw "Error creating Row";
    });
}


fetch(uri + '?query="ID' + productCode + '"', options)
  .then(function (res) {
    return res.json();
  }).then(function (json) {
    if (json.totalCount == 0) {
      AddRow(uriToAdd, optionsToAdd);
    } else {
      let array = json.results;
      let rowId = 0;
      for (let index = 0; index < array.length; index++) {
        if (array[index].objectType = "row") {
          rowId = array[index].objectId
          break;
        }
      }
      callback(null, { "rowId": rowId, "method": "updated" });
    }
  });

Getting Information from Column ID on Smartsheet

To get the column id from Smartsheet I recommend to use Postman API, we will only need the sheet id and the row id.

Request type: GET

{sheetId}: the sheet id from Smartsheet
{rowId}: the row id from Smartsheet
{tokenId}: the token you obtain from smartsheet
 
URI: https://api.smartsheet.com/2.0/sheets/{sheetId}/rows/{rowId}

Headers:
Authorization : Bearer {tokenId}

image