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

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

CodePudding user response：

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

  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:

CodePudding user response：

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