Bridge - JSON parsing - Remove HTML and match date

abrae005

I'm looking for some help on how to extract particular data from a JSON response from an external API call made with Smartsheet Bridge.

The API call is working fine, and I'm getting the JSON response. Part of the response is an indexed set of data within the response, that includes HTML formatting tags. I'd like to strip out the HTML tags and just get the data points.

port: Port Canaveral / date: 09 Mar

port: Cozumel / date: 11 Mar

Then, once I have removed the HTML and have the Date and the Port, I'd like to take that information, and write the Port into a Smartsheet Column for "Destination", matching the date extracted with a "Date" column. (The date column will be pre-populated with a year's worth of sequential dates - 1 per row). Here's a mockup. (Not an actual screenshot from my Smartsheet)

I'm very new to using Bridge, any help you could provide would be highly appreciated.

abrae005

I found a cell formula that will help me remove the HTML tags - basically, using the concept from https://community.smartsheet.com/discussion/90980/isolating-all-text-to-the-right-of-a-character-e-g-in-a-string/ to get a cell with just the destination name (and would also work for just the date)

I'm not sure if all the cell formulas used (RIGHT, LEN, FIND, SUBSTITUTE, CHAR) can be used in Bridge, or if it would be better to have bridge write the JSON response to a helper sheet, use the formula on the cell, and then get that cell's value into Bridge.

I also still need help getting the destination data matched up with the correct date.

abrae005

For those who come across this future searching:

I ended up using a modified version of Brian_Richardson's code from the community topic linked below to parse the JSON API response, and included some string functions in the Javascript to remove the HTML tags.

https://community.smartsheet.com/discussion/97621/use-javascript-in-bridge-to-efficiently-import-data-from-an-api

Brian_Richardson

I wish I had run across this earlier! Nice that you found a solution. I would have suggested exactly the same thing...and used ChatGPT or similar to write some code to parse out the headers. Which is sounds like you are doing. Mind sharing the code snippet where you did that?

abrae005

Hi @Brian_Richardson, sure, happy to share. Here's the first script, which will take the JSON response from the API call, parse out the Date/Location data, remove the HTML, and reformat the date - then put it into an array.

function parseCruiseData(data) {
  const parsedData = [];
  for (const key in data) {
    const item = data[key];

    // Remove HTML tags from date and port
    const dateString = item.date.replace(/(<[^>]*>|&nbsp;|&amp;)/g, "").substring(0, 6);
    const portName = item.port.replace(/(<[^>]*>|&nbsp;|&amp;)/g, "").trimStart();
    
    const fullDate = dateString + "2024"

    // Extract date and format in yyyy-MM-dd
    // const dateParts = dateString.split(" "); // Split by space
    const formattedDate = new Date(fullDate).toISOString().split('T')[0];

    parsedData.push({
      dateValue: formattedDate,
      PortName: portName,
    });
  }
  return parsedData;
}

const parsedCruiseData = parseCruiseData(cruiseData);
// console.log(parsedCruiseData);
return parsedCruiseData;

And here's the 2nd javascript, which will take the the arrays from the first js, and combine them all into one big array, with a line for each date. I can then pass this to UpdateRows, based on a date match, to update my existing sheet.

// Combine data from all sources and extract unique, sorted date values
const allData = source1.concat(source2, source3, source4, source5);
const uniqueDates = Array.from(new Set(allData.map((item) => item.dateValue))).sort((a, b) => new Date(a) - new Date(b));

// Create the multidimensional array
const result = [];

// Loop through each date and find matching port names
uniqueDates.forEach((date) => {
  const port1 = source1.find((item) => item.dateValue === date)?.PortName || "";
  const port2 = source2.find((item) => item.dateValue === date)?.PortName || "";
  const port3 = source3.find((item) => item.dateValue === date)?.PortName || "";
  const port4 = source4.find((item) => item.dateValue === date)?.PortName || "";
  const port5 = source5.find((item) => item.dateValue === date)?.PortName || "";

  result.push([date, port1, port2, port3, port4, port5]);
});

return result;

I still end up having to call the UpdateRows child workflow once for each row in the 2nd array, but it's better than having to call it once for each row multiplied by once for each API response (separate columns)