CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nagaram92
Nagaram92

Reputation: 173

How to search columns for a specific value and move the target row to the top?

I am trying to search column E for a cell starting with "X". I then want to move that entire row up to the top.

This is what I've created so far, using IndexOf:

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var Today = spreadsheet.getSheetByName("Today");

  var TodayList = Today.getRange('E:E').getValues();

  var i = TodayList.indexOf("X", 0);  
  Today.moveRows(Today.getRow(i), 1);

Upvotes: 1

Views: 182

Answers (2)

Cooper
Cooper

Reputation: 64100

Find X rows and move to top

function funko() {
  const ss = SpreadsheetApp.getActive();
  const tsh = ss.getSheetByName("Today");
  const tvs = tsh.getRange(1, 1, tsh.getLastRow(), tsh.getLastColumn()).getValues();
  let a = [];
  let d = 0;
  tvs.forEach((r, i) => {
    if (r[4] == "X") {
      a.push(r)
      tsh.deleteRow(i + 1 - d++);
    }
  });
  tsh.insertRowsBefore(1,a.length)
  a.reverse();
  tsh.getRange(1,1,a.length,a[0].length).setValues(a);
}

Upvotes: -1

Tanaike
Tanaike

Reputation: 201503

In your situation, how about the following modification?

In the case of Array.prototype.indexOf(), the values cannot be directly checked from the 2-dimensional array. But, in your situation, I thought that the 1st character can be directly checked using the index as follows.

Modified script:

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var Today = spreadsheet.getSheetByName("Today");
  var TodayList = Today.getRange('E1:E' + Today.getLastRow()).getValues();
  TodayList.forEach(([e], i) => {
    if (e[0] == "X") {
      var row = i + 1;
      Today.moveRows(Today.getRange(`${row}:${row}`), 1);
    }
  });
}

  • When this script is run, the values are retrieved from column "E". And, each cell value is checked from the retrieved values. When the 1st character of the cell value is "X", the row is moved to the 1st row.

  • In this modification, the lower rows are moved to the upper row. If you want to do this with the reverse, please modify it as follows.

    • From

        TodayList.forEach(([e], i) => {
    if (e[0] == "X") {
      var row = i + 1;
      Today.moveRows(Today.getRange(`${row}:${row}`), 1);
    }
  });

    • To

        var len = TodayList.length;
  var offset = 0;
  TodayList.reverse().forEach(([e], i) => {
    if (e[0] == "X") {
      var row = len - i + offset;
      Today.moveRows(Today.getRange(`${row}:${row}`), 1);
      offset++;
    }
  });

References:

Upvotes: 2

Related Questions