Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
3

With the formula TEXT(number, format) we can force the display of zeros. In my case the formula is now: =TEXT(IMPORTXML(linkOfData,xpath), "000000000")


3

In addition to the remark made by Barry, here's a link that explains the usage of XML in combination with Google Apps Script (GAS): XML with GAS You can use the importXML formula in GAS, but you have to address it as a formula: function myXML() { var cell = SpreadsheetApp.getActiveSpreadsheet() .getActiveSheet().getActiveCell(); cell.setFormula('=...


3

So to start, you want to double each of the quotes inside of your HTML tags for it to show up properly and not cancel out: so class="p1" would be class=""p1"" then for for the the list of p1 elements , you can use regexreplace to surround your data with the tags instead of concatenation (its just a little cleaner in my opinion) So the regex for ...


2

Short answer You are right, the import functions updates their result at certain intervals. Explanation From Change a spreadsheet's locale, time zone, recalculation, and language, a help article from the official documentation, Note: External data functions recalculate at the following intervals: ImportRange: 30 minutes ImportHtml, ImportFeed,...


2

You might try: =index(importhtml("http://bigcharts.marketwatch.com/markets/indexes.asp","Table"),5,4) for the NASDAQ 100 Index and adjust the 5 to suit for others.


2

If ppn is considered to be part of a code, then I would suggest doing the following: Code function ean2ppn(value) { // create array to hold results var output = []; // build URL var url = "http://www.sudoc.fr/services/ean2ppn/"+value+"&format=text/json"; // fetch data as text var txt = UrlFetchApp.fetch(url).getContentText(); // ...


2

Some of the calls are going into multiple cells. Try wrapping each importXML function inside noexpand().


2

This formula worked perfectly for me: =IMPORTXML("http://www.toysrus.com/product/index.jsp?productId=22360626","//*[@id='price']/ul/li[2]/span")


2

The error in your importxml was in the xpath syntax. here is the correct one: =CONCATENATE(IMPORTXML("http://www.walmart.com/ip/Morton-Sea-Salt-Fine-Salt-17.6-oz/10849552","//*[@itemprop='price'][1]")) edited: added the [1] at the end of the xpath to prevent the duplicate price.


2

It's not possible to achieve the desired result by using IMPORTXML alone, I think. Instead use Google Apps Script and the XML Service Service. Example: Adaptation of the code taken from the answer by Vidar S. Ramdal function parseFeedXml(url) { var result = []; var xml = UrlFetchApp.fetch(url).getContentText(); var document = XmlService.parse(xml)...


2

I created a custom import function that overcomes all limits of IMPORTXML I have a sheet using this in about 800 cells and it works great. It makes use of Google Sheet’s custom scripts (Tools > Script editor…) and searches through content using regex instead of XPath. function importRegex(url, regex_string) { var html, content = ''; var response = ...


2

You can pull in all those fields using importxml instead of importhtml: =IMPORTXML("https://es.investing.com/equities/facebook-inc","//*[@class='clear overviewDataTable']/*")


2

I see what the Asker is getting at, and I have an answer and some advice: First, I should warn you, that down this path lies madness. It may seem like something obvious to do, and it doesn't look that hard, but generating HTML using a spreadsheet (be it Google Sheets, Excel or any other spreadsheet package) may seem simple, but things can and will get over-...


2

When importing HTML you have to allow enough blank space (rows and columns) for the imported data. It seems you wish to import a table, so maybe 13 rows and 5 columns per cricketer. Move Sachin down to about Row20, Kumble down to about Row 40, and so on (you seem to have the spare columns needed). If you want only say the matches played you could try ...


2

Maybe: =+importxml("https://www.youtube.com/channel/UC8p19gUXJYTsUPEpusHgteQ","//*[contains (@class, 'view-count')]") If you want the number only (say for further processing): =--REGEXEXTRACT(+importxml("https://www.youtube.com/channel/UC8p19gUXJYTsUPEpusHgteQ","//*[@class='view-count']"),"[\d|,]+")


2

You seem to have forgotten to mention what you would consider tidy, but it should be easier to handle your data (assumed to be starting in A1) with a formula such as: =offset(A1,int(2*((row()-1)/2)),0) in B1 and the same again in C1 with an extra +1 immediately before the ,0 - both copied down to suit. OFFSET INT ROW


2

In my environment, values from tags couldn't be retrieved. If your situation is the same with me, for example, how about this? Please think of this as one of several answers. https://phlanx.com/engagement-calculator?insta=accelerateokanagan is put in a cell "A1". =INDEX(ARRAYFORMULA(REGEXEXTRACT(IMPORTXML(A1, "//body"),"@accelerateokanagan ENGAGEMENT RATE (\...


1

EDIT: So If your interested in trying google apps script, here is short simple script that grabs the piece you want: function getOpenInterest(url) { var found, html, content = ''; var response = UrlFetchApp.fetch(url); if (response) { html = response.getContentText(); if (html) content = html.match(/OPEN INT:<BR><FONT size=1 color='#...


1

Short answer AFAIK, regarding XPath queries to be used with IMPORTXML there isn't straightforward method as XPath 1.0 support looks that was not fully implemented and the web pages developers could follow the practices to set the structure of their webpages. Explanation While the use of tools like Chrome Developer Tools or browser extensions/add-ons could ...


1

Use COUNTA and ARRAY_CONSTRAIN to set the size of the sequential number array to be used as the sort column. Example: =ARRAYFORMULA(ARRAY_CONSTRAIN(ROW(A:A),COUNTA(IMPORTXML(range,xpath)),1)) The final formula should look like the following: =ARRAYFORMULA( ARRAY_CONSTRAIN( SORT( { IMPORTXML(url,xpath), ARRAY_CONSTRAIN(ROW(...


1

Short answer =TRANSPOSE( QUERY( TRANSPOSE( IMPORTXML( "http://chat.stackexchange.com/transcript/7/2015/8/13", "//div[@class='content']" ) ), , 1000 ) ) Explanation IMPORTXML is returning an array having one column by each inner span tag. There is one span tag by each smart link included in the chat ...


Only top voted, non community-wiki answers of a minimum length are eligible