Is it possible to use winhttp to download GAS json file in google drive

Question

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.

Answer 1

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