-1

My goal is to get data into my spreadsheet from my script that mimicks the way IMPORTXML works. I want to return html from my script and consume it with IMPORTXML.

It's disabled now, but at the time I had the issue, my web app get url was working fine:

https://script.googleusercontent.com/macros/echo?user_content_key=HUwieZWDG7ui3qnlqjAZFMl25Yceo2PRDcegGSWRL8ktB6i3-TC7zOtIe9Ca2RWMjw2MPJc0Cs0yidF6Cw36bqfYiUO057r4m5_BxDlH2jW0nuo2oDemN9CCS2h10ox_1xSncGQajx_ryfhECjZEnIItX_WLmfHeqBA_3nangFUYJYkb0VWmsEkuYz81bV5JublgpC3sl6h6fDA1oKmCRrxy_u9Yn2NR&lib=Mt5DyfMUMPjCE3tUjO0cOF-YWmZCruFAP

(It's pretty slow), but it simply returned some simple html. The authorization on my web app was set to anyone, even anonymous. Here's the browser output:

This XML file does not appear to have any style information associated with it. The document tree is shown below.
<body>
<div>boy</div>
<div>girl</div>
</body>

But, when I tried to use this from IMPORTXML like this:

=IMPORTxml("https://script.googleusercontent.com/macros/echo?user_content_key=HUwieZWDG7ui3qnlqjAZFMl25Yceo2PRDcegGSWRL8ktB6i3-TC7zOtIe9Ca2RWMjw2MPJc0Cs0yidF6Cw36bqfYiUO057r4m5_BxDlH2jW0nuo2oDemN9CCS2h10ox_1xSncGQajx_ryfhECjZEnIItX_WLmfHeqBA_3nangFUYJYkb0VWmsEkuYz81bV5JublgpC3sl6h6fDA1oKmCRrxy_u9Yn2NR&lib=Mt5DyfMUMPjCE3tUjO0cOF-YWmZCruFAP","//*")

I got an error:

Error Could not fetch url: https://script.googleusercontent.com/macros/echo?user_content_key=HUwieZWDG7ui3qnlqjAZFMl25Yceo2PRDcegGSWRL8ktB6i3-TC7zOtIe9Ca2RWMjw2MPJc0Cs0yidF6Cw36bqfYiUO057r4m5_BxDlH2jW0nuo2oDemN9CCS2h10ox_1xSncGQajx_ryfhECjZEnIItX_WLmfHeqBA_3nangFUYJYkb0VWmsEkuYz81bV5JublgpC3sl6h6fDA1oKmCRrxy_u9Yn2NR&lib=Mt5DyfMUMPjCE3tUjO0cOF-YWmZCruFAP

What do I need to change to get this working?

I got the same results no matter what IMPORT spreadsheet function I try.

Here's my doGet method:

function doGet(e) {
    return ContentService
    .createTextOutput("<body><div>boy</div><div>girl</div></body>")
    .setMimeType(ContentService.MimeType.XML);
}

Note: I saw the word "girl" flash on my spreadsheet for a second one time, but not again and I don't know why it did. So I think this should be possible.

A Clue: I traced it in the browser, and when it works in the browser the server does a couple of redirects (302) first before giving a 200 and the data.

Improve this question
5
  • 1
    The first link doesn't work
    – Rubén ♦
    Dec 20, 2019 at 3:26
  • @Rubén, thank you for looking at this. I had to disable my web app because my time-based triggers no longer fired once I had it published as a web app. (Maybe you know why that is). I altered my question. I believe this issue should be reproducible with any web app that has my same doGet code because the url does 302s before it does a 200, but I'm not sure and there might be another way, hence the question,.
    – toddmo
    Dec 20, 2019 at 18:26
  • The URL included isn't a Google Apps Script web application URL It looks like the temporary URLs used by Google to serve some types of content. That is very likely to be the reason that explains why your triggers stop working (a temporary link could work for some time but then it will not work anymore)..
    – Rubén ♦
    Dec 20, 2019 at 20:03
  • @Rubén, you are correct that the web app url was different and this is a redirect url. Good point. If you use the actual web app url as given when you publish the web app, you should find that you still have this issue of which I speak.
    – toddmo
    Dec 20, 2019 at 20:50
  • Please edit the question to make it clear what is really this question about.
    – Rubén ♦
    Dec 20, 2019 at 23:15

1 Answer 1

Reset to default
0

As to my goal of mimicking IMPORTXML but the data comes from my script app, I found a work-around. An alternative way of doing it.

If I make a function in my app like this:

function testBabyColors() {
  return [['Boy','blue'],['Girl','pink']];
}

then call that from the surface of my spreadsheet like this =ArrayFormula(testBabyColors()), it will give me tabular data spanning multiple rows / columns, and I can refine that by wrapping that in Query.

In the case of my actual problem which gave rise to the question, on the back end I had a 3rd party website api call that returns some javascript encoded html, instead of just html, so IMPORTXML didn't work. So as part of my work-around, my script actually had to convert the html into an array of values for my sheet to consume.

Had I been able to get my original url working as stated above, IMPORTXML would have saved me the trouble of having to shred the html into an array of values, because that's part of what that function does.

Improve this answer
1
  • I don't think that this answers the question.
    – Rubén ♦
    Dec 20, 2019 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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