Use Javascript in Bridge to efficiently import data from an API
Comments
-
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.
-
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.
-
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))
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives