Insert row before a row in google sheet

Question

I have a google sheet with a lot of rows. For a better view of the sheet, I want add a blank row before every row who have a data in the cell A.

I guess I need use insertRowsBefore.

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

Of course, this function work only for the 2nd row, I don't find the good solution to do that for every row of a data in A.

Someone has a solution or a tips to help me?

:3

Answer 1

From your question and comment, I could understand that for [["value1"], ["value2"], ["value3"]], you want to insert the blank rows like [["value1"], [""], ["value2"], [""], ["value3"]], and each blank line is column A to the last column. If my understanding is correct, how about this modification?

Modification points :

• I think that you can insert rows using insertRowsBefore() by for loop as your script. But in this case, when it is the large number of rows, the process speed will be a bit slow. So I would like to propose to use Sheets API. By this, you can achieve it with one API call.

When you use this modified script, please enable Sheets API at Advanced Google Services and API console as follows.

Enable Sheets API :

Enable Sheets API v4 at Advanced Google Services

• On script editor
  • Resources -> Advanced Google Services
  • Turn on Google Sheets API v4

Enable Sheets API v4 at API console

• On script editor
  • Resources -> Cloud Platform project
  • View API console
  • At Getting started, click Enable APIs and get credentials like keys.
  • At left side, click Library.
  • At Search for APIs & services, input "sheets". And click Google Sheets API.
  • Click Enable button.
  • If API has already been enabled, please don't turn off.

If now you are opening the script editor with the script for using Sheets API, you can enable Sheets API for the project by accessing this URL https://console.cloud.google.com/apis/library/sheets.googleapis.com/

Modified script :

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.
}

Input :

Output :

Note :

• When the rows have no values for the column "A", the rows are skipped.
  • If you don't want to skip it, please remove if (e[0]) { and } else {adjustment++; requests.pop();}.

References :

• Advanced Google Services : https://developers.google.com/apps-script/guides/services/advanced
• Sheets API v4: https://developers.google.com/sheets/api/

If I misunderstand your question, I'm sorry.

Edit 1 :

I changed the method for creating requests for BatchUpdate. The modified script became simpler by creating the requests from the last row. I think that by this, the script can satisfy the output you want.

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

Edit 2 :

This modified script gives the while color to the added new blank row. For this, repeatCell is used.

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