Skip to content

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?

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