Skip Empty Cells in Google Sheets for Text Script with Hyperlink

Question

I have a script that I created in Google Apps Script that sends a slack message to a user with certain IDs on a list in a Google Sheet with a hyperlink. The list for each user does not contain the same number of IDs resulting in the hyperlink url for empty cells to appear in the slack message. Is it possible to skip or hide the hyperlink url for cells that are empty for each user. Apologies if this is not clear. Please see payload script below:

// custom slack webhook
   var url = "https://hooks.slack.com/services/XXXXXSBGW/XXXXXXU1K/XXXXXXXXn9jfZXAAAAAA";
  
  var payload = {
 "channel": "@"+city[2],
 "username": "Alerts",
 "text": "Hi " + city[0] + "\n"+
    "\n Here are your most pending kits for review.\n" + 
    "\n <https://google.com/maps/'"+"|"+city[5]+">" +   
    "\n <https://google.com/maps/'"+"|"+city[6]+">" + 
    "\n <https://google.com/maps/'"+"|"+city[7]+">" + 
    "\n <https://google.com/maps/'"+"|"+city[8]+">" +
    "\n <https://google.com/maps/'"+"|"+city[9]+">" + 
    "\n Please review these kits as soon as possible.\n" 
  };

Generic Hyperlinks provided but basically columns 7 through 9 on the City spreadsheet are sometimes blank. Is it possible to skip these cells if blank or at least not make the url appear? When the cells are blank, usually the hyperlink URL is displayed in the slack message. Any guidance would be much appreciated.

Diego Diego · Accepted Answer · 2020-09-18T21:26:53

You need to check each of those values to see if they're populated and only then add them to the message. Multiple ways you can do this, but maybe the most straightforward is to loop through the values, build an array, and then join the array.

// Example city array
var city = ["name", 1, 2, 3, 4, "city1", "city2", "", "city3"];

// Compile links
var linkStartIndex = 5;
var linkEndIndex = 9;
var links = [];
for (var i = linkStartIndex; i <= linkEndIndex; i++) {
  var value = city[i]
  if (value != null && value != "") {
    links.push("\n <https://google.com/maps/'"+"|"+value+">");
  }
}

// Compile full text
var text = [
  "Hi " + city[0] + "\n",
  "\n Here are your most pending kits for review.\n",
  links.join(""),
  "\n Please review these kits as soon as possible.\n"
];
var joined = text.join("");

console.log(joined);

You could potentially make that a little cleaner using map(), but it all really depends on how your data is structured. Personally, I don't like having a variable number of columns as they doesn't convert as neatly into arrays as variable rows do. I also prefer to work with objects where possible. Consider how generating this object from your city array might make your code easier to comprehend:

var record = {
  name: 'Brian',
  slackId: '123',
  cities: [
    'New York',
    'London'
  ]
};

Skip Empty Cells in Google Sheets for Text Script with Hyperlink

1 Answers