More text functions?

#1

Hi,

I was testing Googe Sheets API.

I was able to get the result: the data from a sheet. This is the place when I got into a problem.

Here’s a part of the resulting JSON I wanted to parse:

parse_no

JSON url is:
https://sheets.googleapis.com/v4/spreadsheets/1VY157ykKsCVDqEKDBp3oAVaG0LTXAz8wUCggCrFXMDM/values/About!A1:A2

It appeared to be a text looking similar to JSON, but parse function =PARSE(B13,"range") did not work. The only result I got was a text:
parse_no2

The formula in B13 is
=PARSE(A13,"['content']"),
it returns the text:
{\n \"range\": \"About!A1:A2\",\n \"majorDimension\": \"ROWS\",\n \"values\": [\n [\n \"See more tricks on:\"\n ],\n [\n \"https://sheetswithmaxmakhrov.wordpress.com/\"\n ]\n ]\n}\n

It added extra new lines \n in the text. I’ve read this article and now I guess the way I could solve this is by using more text functions:

substitute
regexreplace
regexextract

#2

Hi Max,

I couldn’t reproduce the part of the JSON that you show in the screenshot when I tried here.

Can you send me a link to the endpoint you are using?

I assume you then want to parse the data into a range of cells in dashdash, correct?

Torben

#3

hello @makhrov.max.
we have scheduled this for fixing. thanks for sending!

please do send the endpoint @Torben asked for, so that we debug with your specific use case in mind.

:robot:

#4

Thank you Torben!

Correct, I’ve tried to get the range of cells (A1:A2) and to paste them in a dashdash range.

JSON url is:
https://sheets.googleapis.com/v4/spreadsheets/1VY157ykKsCVDqEKDBp3oAVaG0LTXAz8wUCggCrFXMDM/values/About!A1:A2

I’ve also edited my answer, added my url.

I’m also interested in security: how to change my password and how to store API keys secretly. Do I need to open a new discussion?

#5

Thank you, @makhrov.max!

As @humberto said, we will fix this behavior of the parser in such cases.

Regarding security:

  • Password change: You can change your password using the “Forgot password?” link on the login screen.
  • API keys: If you are using one of our integrations, you can store your API key securely when activating an integration. If you want to use generic requests, your API keys would be stored in any cell, e.g. in a separate View.
#6

@makhrov.max I was checking the Google Spreadsheets API, in particular the values/ get method. Comparing it to the response you received, it seems like you called =GET(url, headers, 2) or =POST(url, headers, body,2), that is, you used the last parameter.

That parameter is used to return the whole HTTP response, which you do not need. You only need the actual payload.

Try the same request, but instead remove that last parameter. So, call =GET(url, headers) or =POST(url, headers, body).

Let me know if this works!