0

I would like to download the Json file from google drive using VBA.

Sub downloadGAS()
    Dim winhttp As Object
    Set winhttp = CreateObject("winhttp.winhttprequest.5.1")
    Dim sURL As String
    sURL = "https://script.google.com/feeds/download/export?id=1u848Q8cABNHjoQ42c8twAaS6SWtrn3NMxUKFsz4TQ6Q_e6rcv-eCpfZe&format=json"
    With winhttp
        .Open "GET", sURL, False
        .setRequestHeader "Content-Type", "text/html; charset=UTF-8"
        .send
        debug.print .responseText
    End With
End Sub

Json content:

{"files":[
    {"id":"2169dc1e-202d-4558-b982-eb70c1336de4",
        "name":"appsscript",
        "type":"json",
        "source":"{\n  \"timeZone\": \"Asia/Hong_Kong\",\n  \"dependencies\": {\n  },\n  \"exceptionLogging\": \"STACKDRIVER\",\n  \"runtimeVersion\": \"V8\"\n}"},
    {"id":"d27d6560-847f-46ba-87a4-af2a2a7d9ff6",
        "name":"Code",
        "type":"server_js",
        "source":"function myFunction() {\n  console.log(\"success\")\n}\n"}
]}

There is a function in the GAS json which is:

function myFunction() {
  console.log("success")
}

Is it possible to ensure winhttp responseText to include the GAS json code, so I can keep the response in a text file.

Thank you for your help.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
8平民
  • 49
  • 5

1 Answers1

0

I'm not sure I understand what you're trying to achieve? But if you just want to save the responseText, then use the function that way.

Sub downloadGAS()
    Dim winhttp As Object
    Set winhttp = CreateObject("winhttp.winhttprequest.5.1")
    Dim objStream: Set objStream = CreateObject("ADODB.Stream")
    objStream.Charset = "utf-8"
    
    Dim sURL As String
    sURL = "https://script.google.com/feeds/download/export?id=1u848Q8cABNHjoQ42c8twAaS6SWtrn3NMxUKFsz4TQ6Q_e6rcv-eCpfZe&format=json"
    With winhttp
        .Open "GET", sURL, False
       .setRequestHeader "Content-Type", "text/html; charset=UTF-8"
        .send
        objStream.Open
        objStream.WriteText .responseText
        objStream.SaveToFile "C:\path2file\google_app.json", 2
        Set objStream = Nothing
      '  Debug.Print .responseText
    End With
End Sub
xShen
  • 452
  • 4
  • 9
  • If you paste the URL to the browser, there will be a json file downloaded. The json file is expected to have the coding of myFunction (as shown in the question). I want to use VBA, preferably by winhttp/xmlhttp method, to download the content of jsonfile to my computer. The file created by your coding does not have the coding of myFunction. Hope I can make myself understandable. Sorry for my poor english. Any idea would be very much appreciated. – 8平民 May 07 '22 at 17:42
  • O.k I saw. This is because when you use a browser, Google recognizes your account and that you are logged in. When you use the software (any software) the first page that is received is the Google login form, To overcome this you can try to change the saved file extension .json to .HTML extension and you will see what is received. To try and resolve this make sure the file is set as shared to all. And set your account to be less secure apps at this link https://myaccount.google.com/lesssecureapps?pli=1 – xShen May 08 '22 at 05:30
  • Thank you for your reply. I used Incognito mode to test your explanation and the google block my request of download json file, just like you mentioned. To be clear, I do set the file as shared to all and set my account to allow access from less secure apps using your link, but sadly google still block the download request. I think I shall try another method to work around it, such as converting the GAS to doc file in google drive, if there is no further possible solution at this time. Thank you xShen. – 8平民 May 08 '22 at 06:36