javascript - 在谷歌表格中的行前插入行

Question

我有一个包含很多行的谷歌表格。为了更好地查看工作表，我希望在单元格 A 中有数据的每一行之前添加一个空白行。

我想我需要使用 insertRowsBefore。

function Insert() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  s.insertRowsBefore(2, 1); // add 1 row before the 2nd
}

当然，这个函数只适用于第二行，我没有找到一个好的解决方案来对 A 中的每一行数据执行此操作。

有人有解决方案或提示来帮助我吗？

:3

Answer 1

从您的问题和评论中，我可以理解，对于 [["value1"], ["value2"], ["value3"]]，您想要插入空白行，例如 [["value1"], [""], ["value2"], [""], ["value3"]]，每个空行是 A 列到最后一列。如果我的理解是正确的，那么这个修改怎么样？

修改点:

• 我认为您可以使用 insertRowsBefore() by for 循环作为脚本插入行。但在这种情况下，当行数较多时，处理速度会有点慢。所以我想建议使用Sheets API。这样，您只需调用一次 API 即可实现。

当您使用此修改后的脚本时，请在高级 Google 服务和 API 控制台中启用 Sheets API，如下所示。

启用表格 API:

在高级 Google 服务中启用 Sheets API v4

• 在脚本编辑器上
  • 资源 -> 高级 Google 服务
  • 启用 Google Sheets API v4

Enable Sheets API v4 at API console

• 在脚本编辑器上
  • 资源 -> 云平台项目
  • 查看 API 控制台
  • 在开始使用时，点击“启用 API”并获取 key 等凭据。
  • 点击左侧的“库”。
  • 在搜索 API 和服务时，输入“sheets”。然后点击 Google Sheets API。
  • 点击“启用”按钮。
  • 如果 API 已启用，请不要关闭。

如果您现在打开脚本编辑器，其中包含使用 Sheets API 的脚本，您可以通过访问此 URL https://console.cloud.google.com/apis/library/sheets.googleapis.com/ 为项目启用 Sheets API。

修改后的脚本:

function Insert() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var values = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues(); // Retrieve values of column A.
  var sheetId = sheet.getSheetId();
  var requests = [];
  var count = 0;
  var adjustment = 0;
  values.forEach(function(e, i) { // Create requests for batchUpdate.
    if (e[0]) {
      count++;
      requests.push({
        "insertRange": {
          "shiftDimension": "ROWS",
          "range": {
            "sheetId": sheetId,
            "startRowIndex": i + count - adjustment,
            "endRowIndex": i + count + 1 - adjustment
          }
        }
      });
    } else {
      adjustment++;
      requests.pop();
    }
  });
  Sheets.Spreadsheets.batchUpdate({"requests": requests}, spreadsheet.getId()); // Using Sheets API.
}

输入:

输出:

注意:

• 当行的“A”列没有值时，将跳过这些行。
  • 如果您不想跳过，请删除 if (e[0]) { 和 } else { adjustment++; requests.pop();}.

引用文献:

• 高级 Google 服务:https://developers.google.com/apps-script/guides/services/advanced
• 工作表 API v4:https://developers.google.com/sheets/api/

如果我误解了你的问题，我很抱歉。

编辑 1:

我更改了创建 BatchUpdate 请求的方法。通过从最后一行创建请求，修改后的脚本变得更简单。我认为这样，脚本就可以满足你想要的输出了。

function Insert() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var values = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
  var sheetId = sheet.getSheetId();
  var offset = 1; // For header
  var requests = [];
  for (var i = values.length - 1; i > offset + 1; i--) {
    if (values[i][0]) {
      requests.push({
        "insertRange": {
          "shiftDimension": "ROWS",
          "range": {
            "sheetId": sheetId,
            "startRowIndex": i,
            "endRowIndex": i + 1
          }
        }
      });
    }
  };
  Sheets.Spreadsheets.batchUpdate({"requests": requests}, spreadsheet.getId());
}

编辑2:

此修改后的脚本为添加的新空白行提供白色颜色。为此，repeatCell已使用。

function Insert() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var values = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
  var sheetId = sheet.getSheetId();
  var offset = 1; // For header
  var requests = [];
  for (var i = values.length - 1; i > offset + 1; i--) {
    if (values[i][0]) {
      requests.push({
        "insertRange": {
          "shiftDimension": "ROWS",
          "range": {
            "sheetId": sheetId,
            "startRowIndex": i,
            "endRowIndex": i + 1
          }
        }
      });
      requests.push({ // Give color to new blank row.
        "repeatCell": {
          "cell": {
            "userEnteredFormat": {
              "backgroundColor": {
                "red": 1,
                "green": 1,
                "blue": 1,
              }
            }
          },
          "range": {
            "sheetId": sheetId,
            "startRowIndex": i,
            "endRowIndex": i + 1
          },
          "fields": "*"
        }
      });
    }
  };
  Sheets.Spreadsheets.batchUpdate({"requests": requests}, spreadsheet.getId());
}