CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nagaram92
Nagaram92

Reputation: 173

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

Using google sheets appscript, I am trying to search column E for a cell starting with "XYZ", I then want to move that entire row up to the top.

This is what I've created so far:

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

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

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

However this will move all entries that start with "X" to the top too. I want to just move entries that start with "XYZ".

Credit to user @Tanaike for helping me up to this point.

Upvotes: 1

Views: 124

Answers (2)

Tanaike
Tanaike

Reputation: 201503

In your situation, how about the following modification?

From:

if (/^X|^XYZ/.test(e[0])) {

To:

if (/^XYZ/.test(e)) {

or

if (e.slice(0, 3) == "XYZ") {

Note:

  • In your script, e of TodayList.forEach(([e], i) => { is the cell value. And, e[0] is the top character of the cell value. By this, /^X|^XYZ/.test(e[0]) is always false. This is the reason of your issue. And, this was my miscopied. So, in order to check the cell value, I modified e[0] to e.

  • For example, if you want to check the rows of both top letters of XYZ and X, you can use if (/^X|^XYZ/.test(e)) {

Reference:

Upvotes: 2

Cooper
Cooper

Reputation: 64100

Move to top

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getValues();
  let a = [];
  let d = 0;
  vs.forEach((r, i) => {
    if (~r[4].toString().indexOf("xyz") || ~r[4].toString().indexOf("xy")) {
      a.push(r);
      sh.deleteRow(i + 1 - d++);
    }
  });
 if(a) {
    sh.insertRowsBefore(1, a.length);
    sh.getRange(1, 1, a.length, a[0].length).setValues(a);
 }
}

You can do it this way as well

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getValues();
  let a = [];
  let d = 0;
  vs.forEach((r, i) => {
    if (r[4].toString().match(/xyz|xy/g)) {
      a.push(r);
      sh.deleteRow(i + 1 - d++);
    }
  });
  if (a) {
    sh.insertRowsBefore(1, a.length);
    sh.getRange(1, 1, a.length, a[0].length).setValues(a)
  }
}

Upvotes: 0

Related Questions