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

user_12072955 0
Viewed 68

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);
google-apps-script google-sheets
2 Answers

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:

user_71086530

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