Is it possible to use IMPORTXML function and modify it with a query?

Question

I am creating a spreadsheet portfolio. I came across some limitation, e. g. that I can not automate the process of importing the data from a website for different stocks. This is since the Index for the stock information on the website is often different from another stock. However there is the pattern that it is the next Index from a defined string e. g. "Branche". This made me wonder if I can automate the process with the Google Apps Script.

I wrote down the steps at first in Google Sheets. Then I formulated the steps in the Google Apps Script. Now I am stuck.

Step 1

=IMPORTXML("https://www.comdirect.de/inf/aktien/detail/uebersicht.html?ID_NOTATION=9386126";"//tr/td[@class='simple-table__cell']")

Step 2

=IMPORTXML(CONCATENATE("https://www.comdirect.de/inf/aktien/detail/uebersicht.html?ID_NOTATION=";"9386126");"//tr/td[@class='simple-table__cell']")

Step 3

=INDEX(IMPORTXML(CONCATENATE("https://www.comdirect.de/inf/aktien/detail/uebersicht.html?ID_NOTATION=";"9386126");"//tr/td[@class='simple-table__cell']");62;1)

Step 4 final product - just an idea not working yet

function import_branche() {
  var url1 = "https://www.comdirect.de/inf/aktien/detail/uebersicht.html?ID_NOTATION="
  var ulr2
  var ticker = "//tr/td[@class='simple-table__cell']"
  Index = find the INDEX with the String == "Branche"


  return Index(IMPORTXML(CONCATENATE(url1;url2); ticker);(Index+1);1)

}

Ideally, I would like to have a function where I only need insert the link of the website and get the result. Here is the index for the information automatically found.

In order to correctly understand your question, can I ask you about the detail information of your script? — Tanaike, Feb 16 '19 at 01:23
Of course. Step 1 to 3 are the Google Sheets function I would use, without writing the function in Google Apps Script. Step 4 are those steps combined with the addition that the index is searched within the new written Google Apps Script function. In this way I do not need to look up the index by myself. This would save me a lot of time. However, I new to write a function in Google Apps Script, hence I do not know, if it this is possible. Thank you for asking. — Mr.Man, Feb 16 '19 at 11:55
Thank you for replying. I noticed that your issue had already been resolved just now. I'm glad for it. — Tanaike, Feb 16 '19 at 23:24
Yes, thanks. Still trying to write the function. This might take me a while, but if I am successfull, I will update it here. — Mr.Man, Feb 17 '19 at 14:48

Rubén · Accepted Answer · 2019-02-16T15:49:54.613

1

Google Apps Script can't execute Google Sheets spreadsheet functions like IMPORTXML, so you have two basic alternatives

Use Google Apps Script to get the result of a IMPORTXML formula from the spreadsheet, then use JavaScript to do the rest of the job
Do the job completely using Google Apps Script and JavaScript

Google docs ImportXML called from script
(javascript / google scripts) How to get the title of a page encoded with iso-8859-1 so that the title will display correctly in my utf-8 website?

edited Feb 16 '19 at 15:49

answered Feb 16 '19 at 15:41

Rubén

34,714
9
70
166

Thanks you. As I understand the only way to achieve this is to use the second option you provided. I guess I need to learn more about the subject. It does not seem that simple as I hoped. – Mr.Man Feb 16 '19 at 16:32

Is it possible to use IMPORTXML function and modify it with a query?

1 Answers1