Calc no longer able to pull data from Google Sheets file on web

[onions]

I’m trying to pull data from a Google Sheets file using using the Sheets > External Links … command. Up until a few days ago, it worked: I would copy the URL

prijzen

into the “URL of External Data Source” field and press enter; and keep the default options (Locale: Automatic; no options selected; “Keep asking” unselected, since I don’t know what it would ask me anyway). At this point, I’d see three items listed under “Available Tables/Ranges”; I’d always leave the first one (which I believe was called “HTML_all”) highlighted. (I’d also have it update every ten minutes.)

Now, however, only two items are listed under “Available Tables/Ranges”. When I continue with the first one, it prints the name of the spreadsheet in the selected cell, as it always had, but that’s it. Up until a few days ago, it always printed the several thousand rows of information below.

I’ve tried many other combinations of options to no avail. The only thing that might shed some light is that when I pick the second item in “Available Tables/Ranges”, called “HTML_tables”, the selected cell is filled with the text: “The link could not be updated.” As for what this might signify (and it’s probably nothing, given that I don’t remember what would happen when things were working as intended), I have no idea, but it’s at least a different result.

So. Does anyone have an idea what might be going on here?

I’m using, I believe, the latest version of Calc on Windows 11:

Version: 25.2.5.2 (X86_64) / LibreOffice Community
Build ID: 03d19516eb2e1dd5d4ccd751a0d6f35f35e08022
CPU threads: 20; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

I updated it this morning from a relatively recent version as well, hoping it would fix the problem. It did not :).

Any suggestions would be appreciated. Thank you!

(Now that I’ve looked at the preview of this post, I see that the link to my Sheets file shows that it is “private”; I’ve just opened the link in a private tab, though, and it looks as though it should be accessible to anyone with the URL.)

[onions]

Not sure how comments appear, but...

I'm continuing to play around in Calc and Sheets. I tried accessing the CSV version of the published Sheets document; this allowed me to pull everything into Calc, but only once: If I check the "update" option, I get the same "The link could not be updated" message I mentioned earlier. (Which wasn't a total surprise: Even though I barely know what I'm doing, the parsing options window that appeared after I entered the URL into the field make me think there wouldn't be anything automatic about pulling a CSV. That said, if it simply used the same parsing options I picked the first time...)

That would be... workable enough... barely -- if I simply turned off the updates and reran the command manually. But when I go back to Sheets > External Links ... and paste the URL into the relevant box, pressing enter doesn't do anything. I have to exit out of Calc completely and reopen the file in order to import the URL again.

[Triskel]

Same problem here

[GrahamLees]

Just tested this using a test sheet and LO Calc.

• In Sheets File ⇒ Download ⇒ OpenDocument (.ods) and save to your local drive.

• Open the saved, downloaded file in Calc

• After importing, review the data for any conversion issues or formatting errors.

[onions]

Hi @GrahamLees, and thanks for the response. I’ve done what you suggested, but I’m trying to figure out what I’m looking at. My understanding is that this is a static version of the Google Sheets file I’m trying to access. Is that correct? I see that the .ods file ostensibly uses Google Sheets-specific function (viz GOOGLEFINANCE()) inside an IFERROR(), but in every case I’ve looked at, the output of the GOOGLEFINANCE() function would be indistinguishable from the value_if_error. Since I wasn’t expecting the Google function to work, I’m assuming I’m looking at the value_if_error.

[GrahamLees]

You may find this LO Q&A helpful in respect to the use of GOOGLEFINANCE() in LO Calc. Sorry, I wasn’t aware you were using that function in your sheet.