Use Javascript in Bridge to efficiently import data from an API

Brian_Richardson

I think it's because your sourcedata as attached is a single object. But this is designed to go through an array of objects like this. Be sure that your reference for sourcedata is to the array of results, not to a single result.

Brian_Richardson

Also just a pro-tip for anyone - if you're tweaking the code and it's not working, paste it to ChatGPT along with an example of your sourcedata and columns arrays and ask it about the error. I find ChatGPT to be very fast and very good at troubleshooting code, for those of us (like me) who aren't really coders.

abrae005

Hi @Brian_Richardson , thanks for the tip.

For future folks who come across this thread, here's how I modified the Javascript for my particular source data (which wasn't technically an array, even though it looked similar to an array). I also included some string functions to remove HTML tags from the sourcedata. There's also a string function to change the format of the date string.

// Build Map of Target Sheet Columns. 
// This JS module parameter is an array of columns from the Get Sheet module, fetching the target sheet. 
// targetColumns is set in Bridge as the array of column data from a Get Sheet step. 
// sourcedata is set in Bridge as an array of users from an API call.

const columnMap = new Map();

targetColumns.map(column => columnMap.set(column.title, column.id))


const rows = []; //Empty array to store cells objects later

for (const itinerary of Object.values(sourcedata)) {

const cust = [];

const rmCustomFieldMap = new Map();

// No custom field processing required in this example, commented out.

// row.custom_field_values.data.map(customField => rmCustomFieldMap.set(customField.custom_field_name, customField.value))

const dateWithoutHtml = itinerary.date.replace(/(<[^>]>| |&)/g, "").substring(0, 6);

const port = itinerary.port.replace(/(<[^>]>| |&)/g, "").trimStart();


const fullDate = dateWithoutHtml + "2024";

// const formattedDate = new Date(fullDate).toLocaleDateString('en-US', { year: 'numeric', month: '2-digit', day: '2-digit' });
const formattedDate = new Date(fullDate).toISOString().split('T')[0];


const resultObject = {

cells: [

{

"columnId": columnMap.get('Date_'),

"value": formattedDate //dateWithoutHtml // Extract date only

},

{

"columnId": columnMap.get('ShipPort'),

"value": port // Extract port name only

}

],

"toTop": true //need a location value - toBottom or toTop or other

}

rows.push(resultObject)

}


// The chunkArray function splits any array into small arrays of 500 chunks.

// This is to allow all Smartsheet API calls to succeed as there is a limit of 500 rows per request.

const chunkArray = (array) => {

const arrays = [];

let i,

j,

temparray,

chunk = 500; // Sets the value to 500 items per array.

for (i = 0, j = array.length; i < j; i += chunk) {

temparray = array.slice(i, i + chunk);

arrays.push(temparray);

}

return arrays;

}


return (chunkArray(rows))