CODEWITHSUNDEEP
arraysgoogle-apps-scriptgoogle-sheetssplit
TipVisor
TipVisor

Reputation: 1092

How to split a data set one below the other?

There is a google sheet coordinate set. It has a lot of coordinate values inside a single cell. As in the picture below. enter image description here Here coordinate as N, E, Z. Here after the first coordinate 1 we have gone to the next line inside the same cell. I tried to separate the ones I wanted for one cell but it didn't work.

    function sepCoordi() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Coordi");

  //get data to array
  var coor = sheet.getRange("A2:B47").getValues();
  var one = coor[0][1];
  Logger.log(one);

  var onesplit = one.split(" ");
  Logger.log(onesplit);  
}

Result

enter image description here

This is how I want it [[80.091821,6.842871,98.23][80.091861,6.842881,106.1][80.091905,6.842894,109.25][....][....]]

Upvotes: 0

Views: 75

Answers (2)

Cooper
Cooper

Reputation: 64062

Restructure Data

function restructure() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Sheet0");
  var vs = sh.getRange("A1:B5").getValues().map(r => r[1].split('\n')).flat().map(e => e.split(','));
  console.log(vs);
  sh.getRange(1,3,vs.length,vs[0].length).setValues(vs);
}

Sheet 0:

enter image description here

Execution log
4:24:45 PM  Notice  Execution started
4:24:45 PM  Info    [ [ '1', '2', '3' ],
  [ '4', '5', '6' ],
  [ '7', '8', '9' ],
  [ '10', '11', '12' ],
  [ '1', '2', '3' ],
  [ '4', '5', '6' ],
  [ '7', '8', '9' ],
  [ '10', '11', '12' ],
  [ '1', '2', '3' ],
  [ '4', '5', '6' ],
  [ '7', '8', '9' ],
  [ '10', '11', '12' ],
  [ '1', '2', '3' ],
  [ '4', '5', '6' ],
  [ '7', '8', '9' ],
  [ '10', '11', '12' ],
  [ '1', '2', '3' ],
  [ '4', '5', '6' ],
  [ '7', '8', '9' ],
  [ '10', '11', '12' ] ]
4:24:46 PM  Notice  Execution completed

Posted into Sheet0:

C D E
1 2 3
4 5 6
7 8 9
10 11 12
1 2 3
4 5 6
7 8 9
10 11 12
1 2 3
4 5 6
7 8 9
10 11 12
1 2 3
4 5 6
7 8 9
10 11 12
1 2 3
4 5 6
7 8 9
10 11 12

Array Methods

Upvotes: 0

Tanaike
Tanaike

Reputation: 201388

In your script and your showing Spreadsheet, how about the following modification?

Modified script:

function sepCoordi() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Coordi");
  var coor = sheet.getRange("A2:B47").getValues();
  
  // I modified below script.
  var values = coor.flatMap(([,b]) => b.split("\n").map(e => e.split(",").map(f => f.trim())));
  console.log(values)
}

Reference:

Upvotes: 2

Related Questions