QueryByName() function from another sheet in google spreadsheet

Question

Is it possible to use the QueryByName() function using another sheet from the same spreadsheet?

=query(nor9609s!A1:Z500,querybyname(A1:Z1,"SELECT `Items_shopid` WHERE `Items_id`>0"))

I got error on item.toLowerCase on line 26.

Data from the other sheets (I try use both upper and lower case header but it's not working):

I am using the QueryByName addon function of Google Sheets.

Update: I tried to change header name to one without underscores (_), still the same error.

https://webapps.stackexchange.com/a/155456/284908

Answer 1

From the question:

1. Formula

   =query(nor9609s!A1:Z500,querybyname(A1:Z1,"SELECT `Items_shopid` WHERE `Items_id`>0"))
   

2. Error message:

   TypeError: item.toLowerCase is not a function (line 26)

Apparently the code line from the QueryByName custom function that in your case is throwing the referrerd error message is the following:

  // Lowercase all heading names so that case errors are accepted
  const headings = columnHeadings[0].map(item => item.toLowerCase());

The error occurs because there some values in A1:Z1 that aren't a string. One the way to fix this is to convert these values to text values. I.E. the following formula use TO_TEXT and ArrayFormula:

=ArrayFormula(query(nor9609s!A1:Z500,querybyname(TO_TEXT(A1:Z1),"SELECT `Items_shopid` WHERE `Items_id`>0")))

NOTES:

The formula shown in the screenshot is not using backticks, it's using apostrophes. Please read carefully the instructions from the referred answer.

You might found helpful the programming section of the following resources:

• Wikipedia's article about backticks / grave accent: https://en.wikipedia.org/wiki/Grave_accent

• Wikipedia's article about apostrophes / single quote https://en.wikipedia.org/wiki/Apostrophe