Advertisement

finding and moving all cells that are in 1 column that have same prefix – google sheet – google app script

This is my first coding project. Been just teaching myself, and am pretty stuck.

I have a column of data in a Google Spreadsheet. It is sorted alphabetically. I need a script that will search the data for any cells that start with the same 8 characters and will move those to a new column.

The code needs to do something like this: “start at row 1 in column A, get first 8 characters from that cell, if any other cells match those first 8 characters (*note the column is already sorted alphabetically, so they should all be next to each other), move those cells to column B”, then loop (i.e. start at next row with data, look at first 8 characters in that cell, if any other cells match those first 8 characters, then move those cells to column C”.

Here is a representation of the data I have.


Blacklist – S02E01
Blacklist – S02E02
Blacklist – S02E02 – 2
Breaking Bad – S01E05
Firefly – S01E01
Firefly – S01E02
Firefly – S01E03
Firefly – S01E04
Firefly – S01E05
Three Billboards – 1
Three Billboards – 2
Three Billboards – 3

I want to end up with this:


	Blacklist – S02E01	Breaking Bad – S01E05	Firefly – S01E01	Three Billboards – 1
	Blacklist – S02E02		Firefly – S01E02	Three Billboards – 2
	Blacklist – S02E02 – 2		Firefly – S01E03	Three Billboards – 3
			Firefly – S01E04
			Firefly – S01E05

I think the best way to accomplish this is to use either TextFinder or getValues in a loop to get a range of all cells that start with the same prefix and then us moveTo. This would all need to be done in a loop, but I am not very good with loops and can’t put it all together.

I tried this, but I am sure there are multiple problems here:

const ss = SpreadsheetApp.getActiveSpreadsheet()
let sss = ss.getSheetByName("Sheet1")
let val1 = sss.getRange(8,4).getValue()
let sval1 = val1.substring(0,8)
var rows = sss.getRange('D8:D500');
var numRows = rows.getNumRows();
var values = rows.getValues();
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[0].length > 1 && row[0].substr(0, 8) == sval1)
sss.getRange(row).moveTo(sheet.getRange("E8"));

Any suggestion?

Advertisement

Answer

function distribute() {
  const sA = ['A', 'B', 'C', 'D', 'E'];//map first letters to columns
  let col = {};
  sA.forEach((l, i) => { col[l] = i + 1 });
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const rg = sh.getRange(1, 1, sh.getLastRow());
  const vs = rg.getDisplayValues().flat();
  rg.clearContent();
  let sObj = {pA:[]};
  let oA = vs.map(e => {
    let l = e.slice(0,1);//take one letter
    if(!sObj.hasOwnProperty(col[l])) {
      sObj[col[l]] = [];
      sObj[col[l]].push([e]);
      sObj.pA.push(col[l]);
    } else {
      sObj[col[l]].push([e]);
    }
  });
  sObj.pA.forEach(c => {
    sh.getRange(1,c,sObj[c].length).setValues(sObj[c]);
  }); 
}

Before:

A
AAAAA123
BBBBB100
BBBBB123
BBBBB232
BBBBB256
CCCCC123
CCCCC278
DDDDD322
DDDDD458
DDDDD788

After:

A	B	C	D
AAAAA123	BBBBB100	CCCCC123	DDDDD322
	BBBBB123	CCCCC278	DDDDD458
	BBBBB232		DDDDD788
	BBBBB256

You could also do it this way:

function distribute() {
  const sA = ['AAAAA', 'BBBBB', 'CCCCC', 'DDDDD', 'EEEEE'];//map prefixes to columns
  let col = {};
  sA.forEach((l, i) => { col[l] = i + 1 });
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const rg = sh.getRange(1, 1, sh.getLastRow());
  const vs = rg.getDisplayValues().flat();
  rg.clearContent();
  let sObj = {pA:[]};
  let oA = vs.map(e => {
    //this is the same kind of structure as a reverse pivot table
    let l = e.slice(0,5);//take 5 letters
    if(!sObj.hasOwnProperty(col[l])) {
      sObj[col[l]] = [];
      sObj[col[l]].push([e]);
      sObj.pA.push(col[l]);
    } else {
      sObj[col[l]].push([e]);
    }
  });
  sObj.pA.forEach(c => {
    sh.getRange(1,c,sObj[c].length).setValues(sObj[c]);
  }); 
}

Yet another way:

Code:

function distribute() {
  const sc = 2;
  const sr = 2
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const rg = sh.getRange(sr, 1, sh.getLastRow() - sr + 1);
  const vs = rg.getDisplayValues().flat();
  //rg.clearContent();
  let col = { pA: [] };
  let sObj = { pA: [] };
  let oA = vs.map(e => {
    let l = e.slice(0, 8);
    if (!col.hasOwnProperty(l)) {
      col[l] = col.pA.length + sc;
      col.pA.push(l);
    }
    if (!sObj.hasOwnProperty(col[l])) {
      sObj[col[l]] = [];
      sObj[col[l]].push([e]);
      sObj.pA.push(col[l]);
    } else {
      sObj[col[l]].push([e]);
    }
  });
  sObj.pA.forEach(c => {
    sh.getRange(sr, c, sObj[c].length).setValues(sObj[c]);
  });
}

Sheet0 before:

COL1	COL2	COL3	COL4	COL5
Blacklist – S02E01
Blacklist – S02E02
Blacklist – S02E02 – 2
Breaking Bad – S01E05
Firefly – S01E01
Firefly – S01E02
Firefly – S01E03
Firefly – S01E04
Firefly – S01E05
Three Billboards – 1
Three Billboards – 2
Three Billboards – 3

Sheet0 After:

COL1	COL2	COL3	COL4	COL5
Blacklist – S02E01	Blacklist – S02E01	Breaking Bad – S01E05	Firefly – S01E01	Three Billboards – 1
Blacklist – S02E02	Blacklist – S02E02		Firefly – S01E02	Three Billboards – 2
Blacklist – S02E02 – 2	Blacklist – S02E02 – 2		Firefly – S01E03	Three Billboards – 3
Breaking Bad – S01E05			Firefly – S01E04
Firefly – S01E01			Firefly – S01E05
Firefly – S01E02
Firefly – S01E03
Firefly – S01E04
Firefly – S01E05
Three Billboards – 1
Three Billboards – 2
Three Billboards – 3

remove the comment on the rg.clearContent() line and you get this:

COL1	COL2	COL3	COL4	COL5
	Blacklist – S02E01	Breaking Bad – S01E05	Firefly – S01E01	Three Billboards – 1
	Blacklist – S02E02		Firefly – S01E02	Three Billboards – 2
	Blacklist – S02E02 – 2		Firefly – S01E03	Three Billboards – 3
			Firefly – S01E04
			Firefly – S01E05

Advertisement