3

I have a very simple Google Script that grabs a CSV file from the internet and puts it into a Google Doc. There is more to it before and after, but here is where the issue arises:

  var csvUrl = "https://data.cdc.gov/resource/muzy-jte6.csv?$limit=6000";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();

It runs automatically every night, and has run without an issue for the last six months. All of a sudden, it no longer works. The script editor provides no reason. The link is perfectly valid and still works. I tried it with a different CSV link from a different website, and it had the same issue.

When I run the script, all it says is:

Exception: Unexpected error: https://data.cdc.gov/resource/muzy-jte6.csv?$limit=6000 (line 8, file "Code") Dismiss

As I mentioned, the script has worked flawlessly well over 150 times since the summer, and it has not changed not has the document changed nor has the API link. My only guess is that there was some permission change on my Google account, but the app still has all the permissions it needs from my Google account.

Please help me understand why the script no longer works and how I can get it working again.

new name
  • 15,861
  • 19
  • 68
  • 114
user7577616
  • 31
  • 2
  • No. It appears they have a similar issue, but there is no solution there. – user7577616 Dec 14 '20 at 02:33
  • 1
    So you got your answer. There is no solution as of now. The issue has been reported as a bug and Google developers are working on that. I would suggest you to star the issue to make it even more popular than already is and you can also get notifications when it will be resolved. – Marios Dec 14 '20 at 02:49
  • 1
    @Marios I would like to propose a current workaround for this. Can this question reopened? – Tanaike Dec 14 '20 at 04:03
  • 1
    @Cooper I would like to propose a current workaround for this. Can this question reopened? – Tanaike Dec 14 '20 at 04:03
  • 1
    @Tanaike I think it's better to have 1 canonical thread(Q/A) for all workarounds. Would it be better to add a answer in that duplicate thread as well, So other users referred to that thread might benefit as well? – TheMaster Dec 14 '20 at 10:30
  • @TheMaster Thank you for your proposal. I have the same think with your proposal. If you have said about [this question](https://stackoverflow.com/q/65233801), in this case, unfortunately, I cannot test OP's URL. But, I thought that this might be useful, I proposed the answer including the information for basic authorization as my guess. [Ref](https://stackoverflow.com/a/65287837) On the other hand, I could test the URL of this question. So in the current stage, I would like to leave this answer for this question. How do you think about it? If I misunderstood your comment, please tell me. – Tanaike Dec 14 '20 at 11:02
  • 1
    @Tanaike That's great. Thank you – TheMaster Dec 14 '20 at 11:51

1 Answers1

3

I had the same situation. At that time, I could noticed that when the built-in function of Google Spreadsheet is used for the URL, the values can be retrieved. In that case, as the current workaround, I used the following flow.

  1. Put a formula of =IMPORTDATA(URL).
  2. Retrieve the values from the sheet.

When above flow is reflected to your URL of https://data.cdc.gov/resource/muzy-jte6.csv?$limit=6000, it becomes as follows.

Sample script:

function myFunction() {
  const url = "https://data.cdc.gov/resource/muzy-jte6.csv?$limit=6000";  // This is your URL.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  sheet.clear();
  const range = sheet.getRange("A1");
  range.setFormula(`=IMPORTDATA("${url}")`);
  SpreadsheetApp.flush();
  const values = sheet.getDataRange().getValues();
  range.clear();
  console.log(values)
}
  • In this sample script, "Sheet1" is used. So please modify it for your actual situation.
  • When above script is run, the values from the URL are retrieved as 2 dimensional array for values.
  • In this answer, I used IMPORTDATA. But for each situation, other functions might be suitable. In that case, please check them.

Note:

  • This is the current workaround. So when this issue was removed, I think that you can use your original script.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165