3

I extract one number from an XML online file with Google Spreadsheets. Sometimes the number starts with a zero but the spreadsheet removes it. I'm sure the format of the cell is 'plain text' (I press 020 and 020 appears), but after entering the formula it's like if it became 'number'.

Assuming this data XML:

<sudoc service="ean2ppn">
<query>
<ean>9782870098585</ean>
<result>
<ppn>080253431</ppn>
</result>
</query>
</sudoc>

Using a formula such as:

=IMPORTXML("http://www.sudoc.fr/services/ean2ppn/9782870098585","/sudoc/query/result/ppn")  

will return 80253431 whereas I would like it to return 080253431. How might I achieve this?

7
  • Please share a doc with us, so that we can analyse the source and what you are exactly doing.
    – Jacob Jan
    Commented Feb 20, 2014 at 17:43
  • Do you want to retrieve a corresponding PPN (Pharmacy Product Number)?
    – Jacob Jan
    Commented Feb 20, 2014 at 18:58
  • So what’s your question?
    – Alex
    Commented Feb 20, 2014 at 20:05
  • 1
    how to extract a number from a XML file and keep the zero at the beginning of the number ? the number I'd like to extract is 052743306 but the cell show 52743306
    – mizolm
    Commented Feb 20, 2014 at 20:53
  • 2
    This sounds like a bug/missing feature in Google Spreadsheets. In other XPath processors, you need to specify if the result is to be interpreted as a string or a number. I tested this with a JSFiddle, and running it in Google Chrome outputs 052743306 and 52743306, respectively. Google Spreadsheets ' XPath processor seems to automatically imply that the result is a number. Commented Feb 21, 2014 at 9:36

2 Answers 2

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")

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();

  // convert to JSON
  var data = JSON.parse(txt);

  // preset result
  var results = data.sudoc.query.result;

  // prepare output 
  if(typeof results.length != "number") {
    output.push(results.ppn);
  } else {  
    for(var i in results) {
      output.push(results[i].ppn);
    }
  }

  // return output to sheet
  return output;
}

Screenshot

enter image description here

Explained

On this website, there's an example on how to use JSON. The code above, builds the URL, fetches the data and parses it. Afterwards, the content of the ppn is returned. Afterwards, you can decide, whether the retrieved value is a string or number.

Add the code under Tools>Script editor, press the "bug" button and you're on the go !!

Restriction

The following quota are applicable for the URLFetchApp (Gmail, Google Apps, GA for Business):

enter image description here

See Google Apps Script Dashboard: https://script.google.com/dashboard

Example

I've created an example file for you: ean2ppn

2
  • thank you ! I was thinking about a solution to do it without formulas, but I've never used Script before. So if you're okay I have questions about scripts : - what's the name of this language ? can we use it outside google ? in my precedent solution I was limited to use a max of 50 importXML() , does the script implies any kind of limit ?
    – mizolm
    Commented Feb 22, 2014 at 10:49
  • @mizolm See my revised answer. You could make a small web app, to hold the data, and send an e-mail (pdf, HTML). I doesn't need to be from within a Google Spreadsheet.
    – Jacob Jan
    Commented Feb 22, 2014 at 12:08

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.