Google-sheets – How to display data from cells, but skip over the blank cells

google sheets

I have a list of scores, and I need to display them concisely. How do I read a long row of cells only some of which have scores in them, and print those scores out?

Example: https://docs.google.com/spreadsheets/d/1nPTBFXaIgpruPgNoWFSBUL2yKaibjeNJNExhnHVr8LE/edit#gid=0

I tried using a combination of arrayformula and if's, but failed pretty terribly : )

Best Answer

I just ended up writing a script.

Tools > Script Editor

function consolidate(scores) {
  var results = []
  scores[0].forEach(function (score) {
    if (score) return results.push(score)
  })
  return [results]
}

scores comes in as an array of arrays (in my case one column of n number of rows), and so you have to send it out as such which is why the return [results] results is in brackets.

-Sigh- I love JavaScript.

Related Topic