In Sheets Scripts Editor, how do I count the number of columns with data in them in a specific sheet?

  • Does the sheet has blank cells in the first row between the first and the last column with data? P. S. Please show what you tried. – Rubén Mar 29 at 14:21

This will do that:


const columnsWithData = () => {
  const sh = SpreadsheetApp.getActiveSheet()
  const range = sh.getDataRange()
  const values = range.getDisplayValues()
  const transposeCheck = values[0].map((_, iCol) => values.map(row => row[iCol]).some(cell => cell))
  const countBoolean = transposeCheck.filter(Boolean).length   
  return countBoolean  


This is how the scrip reads:

  1. get active sheet
  2. get the data range
    • this already reduces the number of none empty columns
  3. get the values
    • if you want to ignore the values, shown by formulae, then you can use getValues()
  4. transpose the array and check whether a cell in the column range is not empty
  5. get the count by filtering for true
  6. return the count
  • Thanks Jacob, just running into an implementation problem here – Steve Mar 30 at 11:43
  • @Steve this is a cascade problem and not part of the solution I provided. – Jacob Jan Tuinstra Mar 30 at 11:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.