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?

  • Please share a doc with us, so that we can analyse the source and what you are exactly doing. – Jacob Jan Tuinstra Feb 20 '14 at 17:43
  • Do you want to retrieve a corresponding PPN (Pharmacy Product Number)? – Jacob Jan Tuinstra Feb 20 '14 at 18:58
  • So what’s your question? – Alex Feb 20 '14 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 Feb 20 '14 at 20:53
  • 1
    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. – Vidar S. Ramdal Feb 21 '14 at 9:36
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

  • 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 Feb 22 '14 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 Tuinstra Feb 22 '14 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.