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}