0

Problem:

In Google Sheets, I use the IMPORTDATA function with an API call to a 3rd party data provider that frequently produces a text string that is longer than the cell limit of 50,000 characters. I have no control over the length of the text string that is provided to me, and when the 50,000 character limit is exceeded, I end up with no data at all.

Question:

Is there any way to use scripting for the API call (to avoid the 50,000 character limit) - and then parse that data to two or more cells so that the cell limit is not exceeded?

Rubén
  • 34,714
  • 9
  • 70
  • 166
blafarm
  • 69
  • 1
  • 10
  • 1
    [Is there a script to bypass 50000 characters for in-cell formula?](https://stackoverflow.com/a/55070275/5632629) – player0 Aug 03 '19 at 18:21
  • Thanks, but that is a completely different application. He has a formula that is too long, I have a text string that is too long -- and is a result of an API call. There is no way to truncate or engineer the text strings provided by my 3rd party API provider. I do, however, appreciate you sharing that link. I had searched and seen it already. – blafarm Aug 03 '19 at 20:13
  • Do you have the option to import the data as a file? – Tedinoz Aug 04 '19 at 06:03
  • No, unfortunately not. It is only available as an =IMPORTDATA API call. – blafarm Aug 04 '19 at 07:25

1 Answers1

0

My suggestion would be to use Google Apps Scripts to make the API call. That will return your data as JSON, a string, or some other data type depending on how the API returns data.

Once you have the data in your Google Apps Script you could split the data out into smaller chunks. There are lots of JavaScript string methods for that. You can convert JSON to a compact string using JSON.stringify(yourJSONObject). Once you have your data split out how you want, you can use the Google Sheets API (https://developers.google.com/sheets/api/quickstart/apps-script) to insert the cells you need.

On a side note, I know it says the character limit is 50K everywhere you look, but I have been adding cells that have close to 500K characters in them. Perhaps that is because it is my work account which has G Suite. I would like to read some documentation somewhere on why I can enter way more than 50K characters in a cell.

Documentation on API Calls in Google Apps Scripts:

King Holly
  • 846
  • 8
  • 19