Google sheets script that add a row

2
votes

I don´t know how to code or write scripts.

I have a recording script/macro that order/sort a range according to 2 conditions and is working great for my needs.

I would like however to add a functionality but don't know how to do it, I would like the script would add a blank row between the 1 of the conditions (column 34).

The first condition of the below script is to order/sort according to 2 words in column 34 (or AH), is it possible between that "frontier" to automatically add a blank row?

This is the code I have:

/** @OnlyCurrentDoc */

function Navios() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('u3:Ak').activate()
  .sort([{column: 34, ascending: true}, {column: 22, ascending: true}]);
};

This is a example sheet (pls ignore the conditional format). I want a blank row between words "A decorrer" and "Concluido" in column AH (or 34)

Can someone help?

Thanks.

1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

I believe your goal as follows.

  • You want to insert new row between the value of A decorrer and the value of Concluido at the column "AH" after the sort script was run in your function Navios().

Modification points:

  • In this case, I would like to suggest the following flow.
    1. Run your script for sorting in the function Navios().
    2. Add a script for inserting new row in the function Navios().
      1. Retrieve values of the column "AH".
      2. Retrieve the boundary of the values A decorrer and Concluido.
      3. Insert new row between the values.

When above points are reflected to your script, it becomes as follows.

Modified script:

function Navios() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var range = spreadsheet.getRange('u3:Ak');  // Modified
  range.activate().sort([{column: 34, ascending: true}, {column: 22, ascending: true}]);  // Modified

  // I added below script.
  var values = range.offset(0, 13, range.getNumRows(), 1).getValues();
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] != values[0][0]) {
      spreadsheet.insertRowAfter(i + 2);
      // spreadsheet.deleteRow(spreadsheet.getMaxRows());
      break;
    }
  }
}
  • In this modified script, when the script is run, a row is added to the sheet. By this, the number of rows are increased every run. When you want to keep the number of rows when the script is run, please use the line of // spreadsheet.deleteRow(spreadsheet.getMaxRows());. By this, when new row is inserted, the bottom row of the sheet is deleted. By this, the number of rows are kept. But I'm not sure about your actual situation. So I suggested it as the additional information.

Reference: