0

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.

3
  • 1
    Welcome to [webapp.se]. Please add a brief description of your search efforts as is suggested in How to Ask. – Rubén Jul 9 '20 at 17:27
  • @Rubén I used Google search with different terms like "google sheets expression with importrange" and similar. Also a search in the google support forum and a search in stackoverflow. Found many great things, but none about combining expressions/formula with importrange. – Butl Nor Jul 10 '20 at 8:51
  • 1
    The term expression is rarely used on posts related to Google Sheets. As you already got an answer I suggest to explore the tags google-sheets and importrange (among the other tags o this site) – Rubén Jul 10 '20 at 14:24
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:


Google Forms


  • 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:


Count output


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:


Linked count


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

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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