1

I want to importxml from more than 50 sites in Google Documents and fill that information into another sheet and preferably overwrite the data already there preventing a clear function. The problem I'm running into however is I'm getting 

={229999999.99;0;0;183000000;169999999.99;209999999.99}

in one cell. How do I split this to be six numbers in different columns but the same row?

This is my code:

    function Xml() {

      var sheet = SpreadsheetApp.getActiveSpreadsheet();
      var s = sheet.getSheetByName('Script');
      var ss = sheet.getSheetByName('TradeIBuy');
      var Num = Browser.inputBox("How many URLs to scrape");


    for (y=0;y<2;y++) {

      for (x=2;x-2 < Num;x++)  {
        //ss.getRange("b4:n400").setValue(""); //Too slow for spreadsheet
        var url = s.getRange(x,1).getValue(); //Grab URL
        s.getRange(2,6).setValue(url); //Move URL into position
        var xpathResult = s.getRange("F3:F8").getValues(); //Grab results from first sheet
       if (y===1){     
         var export = ss.getRange(x+2,2); //Export position
         export.setValue(xpathResult); //Export data to second sheet
         SpreadsheetApp.flush();
       }

      }

     }

    }

    function clear() {
      var sheet = SpreadsheetApp.getActiveSheet();    
      sheet.getRange("a2:b1000").setValue("");
    }

Thanks ~ Chandler

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
user3051364
  • 11
  • 2
  • How do you get and parse XML? (It's not in your code). Did you read through the [XML tutorial](https://developers.google.com/apps-script/articles/XML_tutorial?hl=en)? – Bartek Nov 30 '13 at 08:59
  • Hey! Thanks for the reply, in order to parse the HTML I have the URL moved with var url = s.getRange(x,1).getValue(); //Grab URL s.getRange(2,6).setValue(url); //Move URL into position then it is ran through this in the spreadsheet =iferror(importxml(F2,"//sell/min"),"Please check the page, something went wrong") Then it grabs the results from var xpathResult = s.getRange("F3:F8").getValues(); //Grab results from first sheet PROBLEM HAPPENS HERE (don't know how to do it) Then it exports using export.setValue(xpathResult); //Export data to second sheet The problem I'm having – user3051364 Nov 30 '13 at 16:20
  • Waitaminit... You're reading a 6x1 array `var xpathResult = s.getRange("F3:F8").getValues();` then writing the array to a single cell `export.setValue(xpathResult);`. You _want_ a 1x6 array (aka a row). So, you should transpose the array you've read, then write it to a range using `ss.getRange(x+2,2,1,6).setValues()`. See [this answer](http://stackoverflow.com/questions/16989924/copy-a-spreadsheet-row-to-another-column-using-app-script/16999141#16999141) for a function that will do what you are asking. – Mogsdad Nov 30 '13 at 19:42
  • YES! Thanks! Now I'm just running into the problem of it moving the information before it can read it (exporting the Please check the page, something went wrong part of =iferror(importxml(F2,"//sell/min"),"Please check the page, something went wrong") Any ideas? Everything works, it just works a little too fast for importxml to read the data? Like a delay until importxml is parsed? Thanks for your help! If you edit your answer to this I will accept it as the answer. – user3051364 Nov 30 '13 at 20:37

1 Answers1

0

If I understand your question properly, I think there's a solution without scripting.

=SPLIT(SUBSTITUTE(SUBSTITUTE(ImportXML(blah,blah),"}",""),"{",""),";")

...will change {229999999.99;0;0;183000000;169999999.99;209999999.99} to:

229999999.99    0   0   183000000   169999999.99    209999999.99

... with each number in its own cell.

  • SPLIT breaks a string at the given delimiter character, which is ; in this case.
  • SUBSTITUTE is being used to strip the braces.
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • 1
    I would have loved to do this, but because the data is pulled into the script from a cell, then placed, then the next website is loaded and pulled this does not work as the data that is being pulled is simply numbers, but the var xpathResult = s.getRange("F3:F8").getValues(); puts it into ={0;0;0;0;0;0}. One way I know I could fix it is call F3, F4, F5, F6, F7 and F8 as individual cells, paste them, and reload them but that requires one variable per cell that I'm loading and is very slow. That being said, yes, this is what I need, but I need it scripted! Thanks for your help! ~Chandler. – user3051364 Nov 30 '13 at 17:36