拆分文字和填充列

发布于 2025-02-12 15:39:33 字数 491 浏览 1 评论 0原文

我使用的是通过第三方应用程序添加行自动填充的表。添加一排后，我希望将文本分配给以下空列，分隔符为“”（双空间）。我的应用程序脚本能够正确处理静态表，但是如果一个动态脚本（第三方应用程序在底部不断添加行），则脚本只是拆分最后一行并删除先前拆分文本的单元格内容上方。

如何解决这个问题？该脚本应将文本分为列，因为其在最后一行中添加，而不会删除上面的文本。

function splitColumn() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var commaSheet = ss.getSheetByName('Sheet2');
  var lastRowComma = commaSheet.getLastRow();
  var commaRange = commaSheet.getRange('b1:b' + lastRowComma);
  commaRange.splitTextToColumns('  ');
}

原文

I use a sheet that gets auto-populated by addition of rows by a 3rd party app.
Once a row gets added, I wish to split the text to the following empty columns, the separator being " "(double space).
My app script is able to process a static sheet correctly, but in case of a dynamic one(where the 3rd party app keeps adding rows at the bottom), the script just splits the last row and deletes the cells content of the previously split text above it.

How to solve this issue? The script should just split the text into columns as its added in the last row and not delete the ones above.

function splitColumn() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var commaSheet = ss.getSheetByName('Sheet2');
  var lastRowComma = commaSheet.getLastRow();
  var commaRange = commaSheet.getRange('b1:b' + lastRowComma);
  commaRange.splitTextToColumns('  ');
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

度的依靠╰つ 2025-02-19 15:39:33

这似乎是Google表中文本到列功能的默认行为。如果您从Google电子表格中的菜单中选择功能，则可以执行相同的操作。为了避免它，您可以循环遍历范围并单独拆分单元格。这样，就不会覆盖细胞。例子：

function split() {
  const DELIMITER = "  ";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var commaSheet = ss.getSheetByName('Sheet2');
  var lastRowComma = commaSheet.getLastRow();
  var commaRange = commaSheet.getRange('b1:b' + lastRowComma);
  for(let r = 1; r <= commaRange.getNumRows();r++) 
    commaRange.getCell(r, 1).splitTextToColumns(DELIMITER);
}

This appears to be the default behavior of the text to columns feature in Google sheets. It does the same if you select the functionality from the menu in Google Spreadsheets. To avoid it, you could loop through the range and split the cells individually. That way, no cells will be overwritten. Example:

function split() {
  const DELIMITER = "  ";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var commaSheet = ss.getSheetByName('Sheet2');
  var lastRowComma = commaSheet.getLastRow();
  var commaRange = commaSheet.getRange('b1:b' + lastRowComma);
  for(let r = 1; r <= commaRange.getNumRows();r++) 
    commaRange.getCell(r, 1).splitTextToColumns(DELIMITER);
}

回复收藏 0 原文

~没有更多了~