google-apps-script - 查找并移动 1 列中以相同字符开头的所有单元格 - 谷歌表 - 谷歌应用脚​​本

Question

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

我之前问过这个问题 -查找并移动 1 列中具有相同前缀的所有单元格 - 谷歌表 - 谷歌应用脚​​本- （细节较少）并得到了很好的答案，但仍然需要一些帮助

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

列中的数据都是超链接公式，例如 =hyperlink("https://www.imdb.com/title/tt3381714/ , "Blacklist - S02E01")，我想保留所有这些数据（超链接和标题)

代码需要执行以下操作：“从 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"));

我的第一个问题的解决方案 -查找并移动 1 列中具有相同前缀的所有单元格 - 谷歌表 - 谷歌应用程序脚本- 涉及一堆数组操作。它工作得很好，但它只复制和粘贴显示值，而不是包含超链接数据的完整公式。

我尝试对其进行编辑以使其工作，但被卡住并且对 JavaScript 数组的理解不足以完全理解该答案。

有什么建议么？

标签： google-apps-scriptgoogle-sheetsgoogle-sheets-api

Answer 1

建议

您可以使用getFormula方法获取单元格的完整公式（它将获取超链接和显示值），然后您可以将其设置为新单元格。

下面是一个示例脚本以获取更多上下文。此示例将遍历列 A 行，然后它将通过以下substring(0,8)方法比较和排序每一行的值：

function sortItems(){
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var row = 1; //Start row
  var column = 2; //Start column

  for(x=1; x <= sheet.getDataRange().getValues().length; x++){ //Loop will start on row 1
    var next = x+1; //Used to get the next row value to compare to the current row value
    if(sheet.getRange("A"+x).getValue().substring(0,8) == sheet.getRange("A"+next).getValue().substring(0,8)){
      Logger.log(sheet.getRange("A"+x).getValue() +" is equal to "+ sheet.getRange("A"+next).getValue()); //Log for review
      sheet.getRange(row,column).setValue(sheet.getRange("A"+x).getFormula());
      row++ //Move to the proceeding row
    }else{
      Logger.log(sheet.getRange("A"+x).getValue() +" is NOT equal to "+ sheet.getRange("A"+next).getValue()); //Log for review
      sheet.getRange(row,column).setValue(sheet.getRange("A"+x).getFormula()); //Add last value on the current column
      row = 1; //Reset the row to 1 for the next column
      column++; //Set the next column
      sheet.getRange(row,column).setValue(sheet.getRange("A"+next).getFormula()); //Add the first value on the new column
    }
  }
}

[更新]

脚本版本 2

function sortItemsVer2(){
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var row = 1; //Start row
  var column = 2; //Start column
  var rawData = [];
  for(x=1; x <= sheet.getDataRange().getValues().length; x++){ //Get all row data on column A into 'rawData' array variable
    rawData.push([sheet.getRange("A"+x).getValue(),sheet.getRange("A"+x).getFormula()]) ;
  }
  sortRawData(sheet, row, column, rawData);
}

function sortRawData(sheet, row, column, rawData){ //Function to sort 'RawData' array
    for(x=0; x <= rawData.length; x++){ //Loop will start on index 0
    var next = x+1; //Used to get the next row value to compare to the current row value
    try{
      if(rawData[x][0].substring(0,8) == rawData[next][0].substring(0,8)){
      Logger.log(rawData[x][0] +" is equal to "+ rawData[next][0]); //Log for review
      sheet.getRange(row,column).setValue(rawData[x][1]);
      row++ //Move to the proceeding row
    }else{
      Logger.log(rawData[x][0] +" is NOT equal to "+ rawData[next][0]); //Log for review
      sheet.getRange(row,column).setValue(rawData[x][1]); //Add last value on the first column
      row = 1; //Reset the row to 1 for the next column
      column++; //set the next column
      sheet.getRange(row,column).setValue(rawData[next][1]); //Add the first value on the next column
    }
    }catch (e){ //This will add the very last data from column A on the last column
      sheet.getRange(row,column).setValue(rawData[x][1]); 
      row = 1; //Reset the row to 1 for the next column
      column++; //set the next column
      sheet.getRange(row,column).setValue(rawData[next][1]); 
    }
  }
}

样本结果

样品表

运行脚本后的样张