Google Sheet: Query to select both Col1 and Col2

1
votes

I'm using Google Sheet to scrape the web data and would like to select both col1 and col2 together. I tried to use the XPath "and" operator to add the selection but it prompts out "Unable to parse query string". How should I edit the code below to make it work?

=QUERY(ARRAYFORMULA(IMPORTXML("https://www.klsescreener.com/v2/announcements/stock/5199", "//div[contains(@class,'card-body')]")),"select Col1 and Col2 limit 10",-1)

2
google-sheetsxpath
Can I ask you about the values you expect? – Tanaike
I want to select all the col data, using the link above, in the second row I want to select both the sentences "OTHERS" (from col1) and "Press release ..." (from col2) – Philip Chow
Thank you for replying. Although I'm not sure whether I could correctly understand about your replying, for example, this modified formula =QUERY(ARRAYFORMULA(IMPORTXML("https://www.klsescreener.com/v2/announcements/stock/5199", "//div[contains(@class,'card-body')]")),"select Col1, Col2 limit 10",-1) is what you want? – Tanaike
By the way, can I ask you about the detail of select of I want to select all the col data you are thinking? For example, you want to activate the columns? – Tanaike
Thank you for replying. In that case, how about this formula? =REGEXMATCH(TEXTJOIN("",TRUE,IMPORTXML("https://www.klsescreener.com/v2/announcements/stock/5199", "//div[contains(@class,'card-body')]")),"ACQUISITION") In this case, when ACQUISITION included in the retrieved data, TRUE is returned. If I misunderstood your replying, I apologize. – Tanaike

2 Answers

1
votes

Please take a look if the formula is showing the result you want?:

=query(IMPORTXML("https://www.klsescreener.com/v2/announcements/stock/5199", "//div[@class='card-body']"),"limit 10")

enter image description here

1
votes

When you want to retrieve 10 rows from div[@class='card-body'], I think that you can achieve it using a xpath as follows.

Sample formula 1:

=IMPORTXML("https://www.klsescreener.com/v2/announcements/stock/5199", "//div[@class='announcements']/a[position()<=10]//div[@class='card-body']")

Result:

enter image description here

Sample formula 2:

When you want to check whether the value of ACQUISITION is included in the retrieved values, how about the following sample formula?

Sample 1:

=REGEXMATCH(TEXTJOIN("",TRUE,IMPORTXML("https://www.klsescreener.com/v2/announcements/stock/5199", "//div[contains(@class,'card-body')]")),"ACQUISITION")
  • This is from the comment.

Sample 2:

=REGEXMATCH(TEXTJOIN("",TRUE,IMPORTXML("https://www.klsescreener.com/v2/announcements/stock/5199", "//div[@class='announcements']/a[position()<=10]//div[@class='card-body']")),"ACQUISITION")
  • In this case, ACQUISITION is retrieved from 10 rows. And, when the value of ACQUISITION is included, TRUE is returned.

Reference: