Google Spreadsheet Script - How to split an Object[][] from .getValues?

Question

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

How do you get and parse XML? (It's not in your code). Did you read through the XML tutorial? — Bartek
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
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 for a function that will do what you are asking. — Mogsdad
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

Mogsdad Mogsdad · Accepted Answer · 2013-11-30T16:43:27

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.

Google Spreadsheet Script - How to split an Object[][] from .getValues?

1 Answers