javascript - 查找并移动 1 列中具有相同前缀的所有单元格 - 谷歌表 - 谷歌应用脚​​本

Question

这是我的第一个编码项目。一直在自学，而且很困。

我在 Google 电子表格中有一列数据。它按字母顺序排序。我需要一个脚本来搜索以相同 8 个字符开头的任何单元格的数据，并将这些单元格移动到新列。

代码需要执行如下操作：“从 A 列的第 1 行开始，从该单元格获取前 8 个字符，如果任何其他单元格与前 8 个字符匹配（*注意该列已经按字母顺序排序，所以它们都应该是彼此相邻），将这些单元格移动到 B 列“，然后循环（即从下一行开始，查看数据，查看该单元格中的前 8 个字符，如果任何其他单元格与前 8 个字符匹配，然后将这些单元格移动到列C”。

这是我拥有的数据的表示。

黑名单 - S02E01
黑名单 - S02E02
黑名单 - S02E02 - 2
绝命毒师 - S01E05
萤火虫 - S01E01
萤火虫 - S01E02
萤火虫 - S01E03
萤火虫 - S01E04
萤火虫 - S01E05
三块广告牌 - 1
三块广告牌 - 2
三块广告牌 - 3

我想结束这个：

	黑名单 - S02E01	绝命毒师 - S01E05	萤火虫 - S01E01	三块广告牌 - 1
	黑名单 - S02E02		萤火虫 - S01E02	三块广告牌 - 2
	黑名单 - S02E02 - 2		萤火虫 - S01E03	三块广告牌 - 3
			萤火虫 - S01E04
			萤火虫 - S01E05

我认为实现这一点的最佳方法是在循环中使用 TextFinder 或 getValues 来获取以相同前缀开头的所有单元格的范围，然后我们 moveTo。这一切都需要在一个循环中完成，但我对循环不是很好，不能把它们放在一起。

我试过这个，但我确信这里有多个问题：

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"));

有什么建议吗？

标签： javascriptgoogle-apps-scriptgoogle-sheets

Answer 1

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]);
  }); 
}

前：

一种
AAAAA123
BBBBB100
BBBBB123
BBBBB232
BBBBB256
中交123
中交278
DDDD322
DDDD458
DDDD788

后：

一种	乙	C	D
AAAAA123	BBBBB100	中交123	DDDD322
	BBBBB123	中交278	DDDD458
	BBBBB232		DDDD788
	BBBBB256

你也可以这样做：

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]);
  }); 
}

还有一种方式：

代码：

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：

COL1	COL2	COL3	COL4	COL5
黑名单 - S02E01
黑名单 - S02E02
黑名单 - S02E02 - 2
绝命毒师 - S01E05
萤火虫 - S01E01
萤火虫 - S01E02
萤火虫 - S01E03
萤火虫 - S01E04
萤火虫 - S01E05
三块广告牌 - 1
三块广告牌 - 2
三块广告牌 - 3

Sheet0 之后：

COL1	COL2	COL3	COL4	COL5
黑名单 - S02E01	黑名单 - S02E01	绝命毒师 - S01E05	萤火虫 - S01E01	三块广告牌 - 1
黑名单 - S02E02	黑名单 - S02E02		萤火虫 - S01E02	三块广告牌 - 2
黑名单 - S02E02 - 2	黑名单 - S02E02 - 2		萤火虫 - S01E03	三块广告牌 - 3
绝命毒师 - S01E05			萤火虫 - S01E04
萤火虫 - S01E01			萤火虫 - S01E05
萤火虫 - S01E02
萤火虫 - S01E03
萤火虫 - S01E04
萤火虫 - S01E05
三块广告牌 - 1
三块广告牌 - 2
三块广告牌 - 3

删除 rg.clearContent() 行上的注释，你会得到：

COL1	COL2	COL3	COL4	COL5
	黑名单 - S02E01	绝命毒师 - S01E05	萤火虫 - S01E01	三块广告牌 - 1
	黑名单 - S02E02		萤火虫 - S01E02	三块广告牌 - 2
	黑名单 - S02E02 - 2		萤火虫 - S01E03	三块广告牌 - 3
			萤火虫 - S01E04
			萤火虫 - S01E05