Google Sheets IMPORTRANGE – Use Expressions or Formulas on Values

google sheetsimportrange

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.

Best Answer

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

Related Solutions

Google Sheets – Using FILTER or IMPORTRANGE to Filter Data from Another Spreadsheet

Use query instead of filter for this:

=query(importrange("URL","A:J"), "select * where Col3 = 'Yes'")

The command query can refer to the columns of the array that it receives in an abstract way: Col1, Col2, and so forth, without being tied to their placement within the sheet. So, it filters the thing "in the cloud" and then puts the result into the sheet.

In contrast, filter can only refer to columns by the cell names like C:C. This means that the array must first be placed into the sheet, and then be filtered in place according to its own values. This runs into circular dependency.

Use INDIRECT Inside IMPORTRANGE Function in Google Sheets

The second argument of importrange must be a string, not a cell reference. The command indirect takes a string and returns a cell reference, which is not what you need. Just use

=IMPORTRANGE("KEY"; "Sheet1!G"&B7)

To clarify the difference:

Strings: "B4", "Sheet1!A3", "Sheet1!A"&E2
Cell references: B4, indirect("Sheet1!A3"), indirect("Sheet1!A"&E2)

Best Answer

Related Solutions

Google Sheets – Using FILTER or IMPORTRANGE to Filter Data from Another Spreadsheet

Use INDIRECT Inside IMPORTRANGE Function in Google Sheets

Related Topic