-1

item.toLowerCase error on line 26

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

Data from another sheets enter image description here

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

5
  • Welcome to Web Applications. It's not clear what you mean by "Its not problem with the script right" specially because QueryByName is not a Google Sheets built-in function. Does it comes from a bounded script or from an add-on?
    – Rubén
    Oct 26, 2021 at 5:39
  • I use your addon function in google script QueryByName() , maybe // Use a regular expression to get a list of all strings inside backticks` let fieldNamesInQuery = queryString.matchAll(/([^]+)/g); cannot use underscore '_' in the column header
    – lochoe
    Oct 26, 2021 at 5:42
  • I'm sorry but I don't know what you mean by "your addon function in google script". My add-ons doesn't include that custom function.
    – Rubén
    Oct 26, 2021 at 5:44
  • im sorry..i ask wrong person...so sorry
    – lochoe
    Oct 26, 2021 at 5:46
  • Instead of adding formulas as image the best is to add them as text. Also is better to add the most simple formula needed to reproduce the problem.
    – Rubén
    Oct 26, 2021 at 6:05

1 Answer 1

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:

2
  • Yes my mistake, i did change to backtick, still same error
    – lochoe
    Oct 26, 2021 at 6:08
  • @lochoe Answer updated.
    – Rubén
    Oct 26, 2021 at 18:16

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.