-1

For various reasons (see question Force the refresh of a Google Sheet) I am trying to replace the use of importData in an app script with a custom function that leverages UrlFetchApp.fetch() retrieve a set of data from a remote host that is in CSV format.

I'm using the UrlFetchApp class to get the response with urlResponse.getContentText() followed by Utilities.parseCsv(), returning that result from my custom function.

This all works as expected with one difference that I'm trying to solve for. Using importData to the same endpoint I get a result to my sheet that automatically detects and handles dates and number columns. With my custom method, this does not happen so numbers are in as text. This creates issues with a summary sheet as their numeric values are expected.

Is there some other method or technique that I should be using to get the same result as I get with importData and that I get (by selecting the right option) by importing CSV from a file?

I created a new sheet - CellarInventoryTestSheet that can be used to see the issue I'm describing. The first tab - "Login" has description and instructions. I also ran the sheet with the two functions and did a copy / paste special values only which shows the difference in the results. Let me know if this is useful.

Improve this question
8
  • 1
    Add a "minimum verifiable complete example" (code, instructions to run it, an a sample of the response retrieved by urlFetchApp, and the corresponding expected result).
    – Rubén ♦
    May 12 at 16:14
  • I understand the ask, the data is being fetched from a site that requires authentication, so I'd need to include Id/pwd info so need to think about that. I can easily provide the output of two sheets that show the difference, but I don't think that will help you much. I'd be happy to share with you privately if that helps.
    – Neil Lamka
    May 12 at 16:33
  • Not necessary to add the real id/pwd... just make it clear that they are required.
    – Rubén ♦
    May 12 at 16:40
  • I know I'm being slow but without the authentication the sheet functions won't run and retrieve any data so I'm not picking up how that helps.
    – Neil Lamka
    May 12 at 17:06
  • 1
    It would be great to get a real answer here of course, that said, I found a solution to my original issue (the referenced question) so no longer need to use the UrlFetchApp and Utilities.parseCsv method as importData works as expected and wasn't using UrlFetchApp.fetch(url) didn't add any value.
    – Neil Lamka
    May 16 at 17:49

1 Answer 1

Reset to default
0

One easy way to coerce numeric text strings to numbers is to Insert > Sheet and put this formula in cell A1 of the new sheet:

=arrayformula( 
  iferror( 
    value('Consumed-URLFetch Static'!A1:AT), 
    'Consumed-URLFetch Static'!A1:AT 
  ) 
)

Then format any date columns as Format > Number > Date.

See the new Reformat sheet in your sample spreadsheet.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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