4
$\begingroup$

I have data in my google sheets which is extracted using:

httpRequest=HTTPRequest[<|
    "Scheme"->"https",
    "User"->None,
    "Domain"->"docs.google.com",
    "Port"->None,
    "Path"->{"","spreadsheets","d","...","gviz","tq"},
    "Query"->{"tqx"->"out:html","tq"->"","gid"->"..."},
    "Fragment"->None
|>]

httpResponse=URLRead[httpRequest]

data=Import[httpResponse,"FullData"][[2]]

But is coming out as follows:

{ {"Date:","3/6","Time In:","11:00","Time Out:","12:30"}, {"Date:","3/13","Time In:","11:00","Time Out:","12:30"}, {"Date:","3/20","Time In:","11:00","Time Out:","12:30"}, ... }

While the actual data is:

{ {"Date:","3/6/2021","Time In:","11:00:00 AM","Time Out:","12:30:00 PM"}, {"Date:","3/13/2021","Time In:","11:00:00 AM","Time Out:","12:30:00 PM"}, {"Date:","3/20/2021","Time In:","11:00:00 AM","Time Out:","12:30:00 PM"}, ... }

My issue is the data coming out from the request is the formatted data rather than the actual data from the cell. How do get the underlying data actually stored in the cells so I can recognize it as Mathematica date and time objects?

$\endgroup$
3
  • $\begingroup$ It's possible that there's a better way to do it than Import[httpResponse,"FullData"], but I cannot reproduce the URLRead. $\endgroup$ Mar 26, 2021 at 10:44
  • $\begingroup$ I don't know how you publish your data, but on my personal account using Publish to the web, publishing data similar to yours with date formats in XLSX and CSV formats, I could receive the whole data. my code : a=URLRead@HTTPRequest[URL]; ImportString[a["Body"], "XLSX"]. Using XLSX will give DateObject but with CSV you should parse a string. $\endgroup$
    – Ben Izd
    Mar 26, 2021 at 11:13
  • $\begingroup$ Can you provide a link to the actual document that you are trying to import? $\endgroup$
    – Lukas Lang
    Sep 17, 2022 at 16:46

1 Answer 1

0
$\begingroup$

Unfortunately, httpResponse = URLRead[httpRequest]returns: "Not Found". Therefore I take the test data from your question.

You can fix the wrong format by the following:

d = {{"Date:", "3/6", "Time In:", "11:00", "Time Out:", 
    "12:30"}, {"Date:", "3/13", "Time In:", "11:00", "Time Out:", 
    "12:30"}, {"Date:", "3/20", "Time In:", "11:00", "Time Out:", 
    "12:30"}};
d = (tmp = #; tmp[[2]] = tmp[[2]] <> "/2021"; tmp) & /@ d;
d = (tmp = #; tmp[[4]] = tmp[[4]] <> ":00 AM"; tmp) & /@ d;
d = (tmp = #; tmp[[6]] = tmp[[6]] <> ":00 PM"; tmp) & /@ d;
d

{{"Date:", "3/6/2021", "Time In:", "11:00:00 AM", "Time Out:", 
  "12:30:00 PM"}, {"Date:", "3/13/2021", "Time In:", "11:00:00 AM", 
  "Time Out:", "12:30:00 PM"}, {"Date:", "3/20/2021", "Time In:", 
  "11:00:00 AM", "Time Out:", "12:30:00 PM"}}
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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