My table has a number of columns, namely G-L, each column contains some item data. I need to write out the total number of each item, in each column, to another sheet.
Here is a simplified GGSheet and some data.
https://docs.google.com/spreadsheets/d/1j8NNdAgOVEzK-FovHJTcj6dnhDjrsdhAx6sVCaRBDa0/edit?usp=sharing
I will calculate the total items of each column using a Query, as written in A2. I then can just copyValueToRange the result. I will have to repeat this process with all of the order column.
To do this, I will have to pass the "next column" parameter into my Query each time I finished writing data. As in A2 :
Select G .....ProceedData()
Select H .....ProceedData()
I'm currently stuck here. I can't figure out how to replace the column inside the query with the "next column" after I proceed data.
Though about string sustitute... but it seems google script doesn't have this function.
Any idea for this solution is very appreciated !
A small note : The number of column that contain data is vary depend on user input. It might come to 100 or more.
In cell A2 I entered this formula
=query(ArrayFormula(substitute(transpose(split(textjoin(" ", 1, trim(query(substitute(G2:L, " ", "_"),,rows(G2:G)))), " ")), "_", " ")), "Select Col1, Count(Col1) group by Col1 label Count(Col1)''")
See if that works for you?