How to use an expression or formula on values from IMPORTRANGE

Question

I am trying to use an expression on the values that are imported from another spreadsheet. The IMPORTRANGE copies the raw data, and with QUERY I can then specify which columns to use, so I can skip some of them.

However, I want to process the imported values by some expression. The QUERY doesn't seem to support different string functions like LEN, etc. But if I use an expression over the QUERY, like "=LEN(QUERY(...", then I get only one record as a result, not a list of rows, as I would from IMPORTRANGE and QUERY.

Specifically, I have a source spreadsheet, that is filled automatically by one of my Google Forms, and there is a column with a list of personal names. This source spreadsheet is restricted to only a few users. But I have create another spreadsheet, for the public use. In this copy, I want to display all the non-sensitive columns, but for the personal names column, I want to display only a number of the names, which are comma-separated. The expression to get the number of the names is otherwise trivial:

=IF(LEN(TRIM(A1)) > 0; COUNTA(SPLIT(A1; ",")); "")

I could use the expression in the source spreadsheet, but it's overwritten by Google Forms. So I need to use the expression in the copied spreadsheet.

What are the possible solutions? I haven't found any questions similar to mine.

Answer 1

I suggest that you build the "count" in a second sheet of the Google Forms Response Spreadsheet, and then reference it using importrange.

Suppose that your Google Forms Response Sheet looks like this:

---

---

• Create a new sheet in the same spreadsheet, say the sheet name = "Count".

• Put a header in Cell A1,

• Insert this formula in cell A2:

=ArrayFormula(if(len('Form Responses 1'!C2:C),len('Form Responses 1'!C2:C)-len(REGEXREPLACE('Form Responses 1'!C2:C,",",""))+1,))

The output should look like this:

---

---

In the external spreadsheet, insert these formula in the following cells:

• Cell A1: =importrange("https://docs.google.com/spreadsheets/d/<<insert spreadsheet id>>","Form Responses 1!A1:B")
• Cell C1: =importrange("https://docs.google.com/spreadsheets/d/<<insert spreadsheet id>>","Count!A1:A")
• Cell D1: =importrange("https://docs.google.com/spreadsheets/d/<<insert spreadsheet id>>","Form Responses 1!D1:E")

The output should look like this:

---

---

Props: infospired.com How to Count Comma Separated Words in a Cell in Google Sheets