WIKIDATASEARCH broken when wrapped in with ARRAYFORMULA

[rajun274]

Hello!

I am a new volunteer for my local library helping with large-scale data processing. In order to hand off an easy-to-use Google Sheet template for them, I have been relying on ARRAYFORMULA for auto-population of rows. However, it seems like WIKIDATASEARCH breaks when wrapped in ARRAYFORMULA.

Problematic formula where nothing loads: =ARRAYFORMULA(WIKIDATASEARCH(A3:A7))
Similar formula that works: =ARRAYFORMULA("Length of string: " & LEN(A3:A7))

Simplified, reproducible Spreadsheet: https://docs.google.com/spreadsheets/d/1gW_NZp6nd2HsZlO9FaVDAbQOT7F0unmSYNNX1kemjYU/edit#gid=0

Thanks in advance for your help!

[tomayac]

Hi @rajun274,

The formula is not ARRAYFORMULA()-capable (might be a limitation of custom formulas, I don't know), but you can simply downfill, as I have done in my example (note the IF() in the formula). It's not as elegant as the ARRAYFORMULA() way, but it works.

Cheers,
Tom

[rajun274]

Hey @tomayac !

Thank you for such a quick reply, and thank you for your suggested solution!

I did some Googling of "google app scripts arrayformula", and there's documentation on how to write custom formulas that accept arrays as input and output arrays. Looking at your source code for WIKIDATASEARCH, I think you just need to see if Array.isArray(search), and if true, do search.map(invokeFunction). Then I can just do =IF(LEN(A3:A) > 0, WIKIDATASEARCH(A3:A), "").

For background, the main reason why I prefer ARRAYFORMULA array inputs: I am creating a template Sheets as a volunteer project for librarians. I have seen first-hand that their computer literacy / technical skillset is significantly lower than ours. I'm trying to make my template as simple as humanly possible; specifically, I would rather not have the librarian have to go to some sheet and ensure all formulas are dragged down enough rows. If I could instead simply do =IF(LEN(A3:A) > 0, WIKIDATASEARCH(A3:A), ""), then it'll automatically copy all the way down, and everything will just work. #ProductExcellence

Cheers, and thanks in advance Thomas!

[tomayac]

Thanks for the pointer to the docs. Would you have the time for a PR that adds this to all functions?

[rajun274]

Hey Thomas!

Ha, I should've figured that'd be the outcome :) I can certainly try, although I basically have never done JS development [or open source development] haha. But based on the Google App Script docs, it should be very straight-forward.

I believe these would be the AIs:

1. I don't see any tests for WIKIDATASEARCH, so maybe I'll start with WIKIDATAQID, especially since it seems simple.
   1. QUESTION: Is there an easy way to test my changes in an Google Sheets? Basically, deploy "locally"? It seems the existing tests only confirm the formula produces a non-empty string.
   2. QUESTION: More generally, how do you set up a dev environment for this?
2. Once I confirm that works, I'll write tests for WIKIDATASEARCH, then upgrade it, and upgrade other functions.
   1. FYI: It seems that an example of =WIKIDATASEARCH is missing from your example Google Sheet. I'll add that in too.
   2. FYI: It seems many of your formulas already produce array results. I'm not sure what format you'd like those formulas to output. (We can also circle back to this later.)

Cheers!

[tomayac]

Ha, I should've figured that'd be the outcome :) I can certainly try, although I basically have never done JS development [or open source development] haha. But based on the Google App Script docs, it should be very straight-forward.

Yeah, I'm sorry. I have stopped developing this library myself since it turned into a huge time sink, but I'm happy to merge PRs and review code.

I believe these would be the AIs:

1. I don't see any tests for WIKIDATASEARCH, so maybe I'll start with WIKIDATAQID, especially since it seems simple.

Yeah, if there's no test, it's probably just an oversight. Sorry for that.

1. QUESTION: Is there an easy way to test my changes in an Google Sheets? Basically, deploy "locally"? It seems the existing tests only confirm the formula produces a non-empty string.

My flow always was to execute the test file in the code editor. I am not sure if there's a better flow now. I didn't really look into it for too long.

2. QUESTION: More generally, how do you set up a dev environment for this?

I think https://issuetracker.google.com/issues/36763437?pli=1 is still not fixed. I have just copied and pasted my functions into a new sheet, as unsatisfactory as it was.

2. Once I confirm that works, I'll write tests for WIKIDATASEARCH, then upgrade it, and upgrade other functions.

Thank you <3

1. FYI: It seems that an example of =WIKIDATASEARCH is missing from your example Google Sheet. I'll add that in too.

Perfect, just granted you editing rights.

2. FYI: It seems many of your formulas already produce array results. I'm not sure what format you'd like those formulas to output. (We can also circle back to this later.)

Yes, some of them do. I have no clue what the output would look like in such cases and if it would even be possible. We'll see…

[rajun274]

Yeah, if there's no test, it's probably just an oversight. Sorry for that.

Not a problem! They should be super easy to write!

My flow always was to execute the test file in the code editor. I am not sure if there's a better flow now. I didn't really look into it for too long.

Yup, that's fine too.

Perfect, just granted you editing rights.

FYI, inserting a new column caused a data re-load of all existing columns, and not all columns were re-populated with data 😰. The no-results formulas say "Loading..." for a while before showing no results, which implies a timeout issue.

[tomayac]

FYI, inserting a new column caused a data re-load of all existing columns, and not all columns were re-populated with data 😰. The no-results formulas say "Loading..." for a while before showing no results, which implies a timeout issue.

This is a super annoying bug, tracked as https://issuetracker.google.com/issues/36759198. It might also be a quota issue. In case an error returns, I return the empty string, which isn't great, but better than returning an error string that might be misinterpreted. I'm not sure if there is a better way to make this fail gracefully.

[ernestobarrera]

I have been using these functions for years. Very grateful for them and any improvement in their performance. In the spreadsheet with the examples, I have detected a problem. Cell S2 [=WIKIDATAFACTS("en:Berlin")] returns a #REF! error because there is a function in S9 [=WIKIDATAFACTS("de:Berlin")] 'Array result was not expanded because it would overwrite data in S9'

[rajun274]

Thanks Ernesto for the information! I think that was leftover text when I was trying to debug why no values were showing up. In fact, I don't see the ERR cell b/c they're still not working for me. Anyway, I've fixed the problem I caused and removed the text from that cell. Thank you!

…

On Wed, Sep 1, 2021, 00:24 Ernesto Barrera ***@***.***> wrote: I have been using these functions for years. Very grateful for them and any improvement in their performance. In the spreadsheet with the examples, I have detected a problem. Cell S2 [=WIKIDATAFACTS("en:Berlin")] returns a *#REF! error* because there is a function in S9 [=WIKIDATAFACTS("de:Berlin")] 'Array result was not expanded because it would overwrite data in S9' [image: QJMFsX4rXl] <https://user-images.githubusercontent.com/44771579/131520165-e82c2a78-6ec0-496b-b42a-775357ab401d.png> — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#41 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AI5OV3SU3SYMILSZWQWEUATT7TQ3DANCNFSM5CY7PP5A> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.