I’ve been utilizing Google Sheets for data extraction from websites. Currently, I am applying the IMPORTXML function as follows:
=importxml(A1, "//div[@class='review-content']//text()")
The output I receive is:
Row1: {"publishedDate":"2019-01-05T22:19:28Z","updatedDate":"null","reportedDate":"null"}
Row2: {"publishedDate":"2018-12-10T22:19:28Z","updatedDate":"null","reportedDate":"null"}
Row3: {"publishedDate":"2018-12-09T22:19:28Z","updatedDate":"null","reportedDate":"null"}
However, I’m struggling to extract just the “publishedDate” values. For instance, I want to achieve:
Row1: 2019-01-05T22:19:28Z
Row2: 2018-12-10T22:19:28Z
Row3: 2018-12-09T22:19:28Z
Could anyone provide guidance on what I might be overlooking?
I encountered a similar issue when extracting specific JSON values into Google Sheets using IMPORTXML. One alternative approach is using the SPLIT function combined with QUERY. If your JSON is consistently structured, you can use SPLIT(A1, ",") to divide the string into separate elements. Then, use INDEX to select the piece you’re interested in that contains “publishedDate”. For additional precision, QUERY can assist in isolating the result. This method requires a few steps, but it’s effective once set up.
hey bob, try using REGEXEXTRACT function to just grab the publishedDate from the string once you got the data into sheets. something like : =REGEXEXTRACT(A1, "publishedDate":"(.*?)",") that should capture the date after the publishedDate key and ignore the rest.