How to find the correct rowId when using a limited range from Google Sheet?-CodePudding

I have the below code that checks through a sheet for the words "CASH". Once it's found, it will copy the row to a new sheet then delete the row.

Prior, my range for the values was set up as so:

var range = ss1.getRange(2, 1, lr, lc);

But now that my sheet has 6000 rows, this is highly inefficient. I've changed it so that it only looks through a range that is lr-100 so that it doesn't have to dig so deep.

Since making that change, my old code to delete rows ss1.deleteRow(i 2) is no longer valid, because i references the row only within that particular range (i.e., if it's the 90th row out of that 100, i = 90, and that would end up deleting the 90th row in my sheet when it should have been, for example, 6500.

question How do I find the correct row# in this new way my script is setup?

var etfar = ["BOTZ"]


function doCash() {
    for (var i = 0; i < etfar.length; i  ) {
    cashMoney(etfar[i])
    }
};

 
function cashMoney(etf) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ss1 = ss.getSheetByName(etf);
  var ss2 = ss.getSheetByName("Cash");

  var lr = ss1.getLastRow();
  var lc = ss1.getLastColumn();

var range = ss1.getRange(lr-100, 1, lr, lc);
var data = range.getValues();

for (var i = data.length - 1; i >= 0; i--)
 {
  var check = data[i][4] // ith row, 3rd column
  if (check.includes("CASH")) { 

 var rowUsd = data[i];
 
// something has to happen here to find the exact row

 ss2.appendRow(rowUsd);
 //ss1.deleteRow(i 2); <-- this is old code. Prior, in my 'range' I used to start my rows from "2", but that was highly inefficient and I've changed it to "lr-100" so that it begins checking from the last 100 rows instead
    }
};
};

CodePudding user response：

I think this is what you want

function cashMoney(etf) {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName(etf);
  var sh2 = ss.getSheetByName("Cash");
  var lr = sh1.getLastRow();
  var lc = sh1.getLastColumn();
  var range = sh1.getRange(lr - 100, 1, 101, lc);
  var data = range.getValues();
  var d = 0;
  for (var i = data.length - 1; i >= 0; i--) {
    var check = data[i][4] // ith row, 3rd column
    if (check.includes("CASH")) {
      var rowUsd = data[i];
      sh2.appendRow(rowUsd);
      sh.deleteRow(lr - 100   i - d  );The row is data start row   i 
    }
  };
}

The d keeps track of the rows that have already been deleted from the spreadsheet which is necessary because they have not been deleted from the data set.

CodePudding user response：

From now that my sheet has 6000 rows, this is highly inefficient., in your situation, in order to reduce the process cost of the script, how about using Sheets API? When Sheets API is used for your situation, all rows might be able to be processed. When Sheets API is used for your script, it becomes as follows.

Modified script:

This script uses Sheets API. So, please enable Sheets API at Advanced Google services. And, please set etfar.

function sample() {
  var etfar = ["Sheet1", "Sheet2",,,]; // Please set your sheet names.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dstSheet = ss.getSheetByName("Cash");
  var dstSheetId = dstSheet.getSheetId();
  var lastRow = dstSheet.getLastRow();
  var { copyRows, deleteRows } = etfar.reduce((o, e) => {
    var srcSheet = ss.getSheetByName(e);
    var srcSheetId = srcSheet.getSheetId();
    srcSheet.getRange("E2:E"   srcSheet.getLastRow()).getValues().forEach(([v], i) => {
      if (v == "CASH") {
        o.copyRows.push({ copyPaste: { source: { sheetId: srcSheetId, startRowIndex: i   1, endRowIndex: i   2, startColumnIndex: 0 }, destination: { sheetId: dstSheetId, startRowIndex: lastRow, endRowIndex: lastRow   1, startColumnIndex: 0 } } });
        o.deleteRows.push({ deleteDimension: { range: { sheetId: srcSheetId, dimension: "ROWS", startIndex: i   1, endIndex: i   2 } } });
        lastRow  ;
      }
    });
    return o;
  }, { copyRows: [], deleteRows: [] });
  var requests = [...copyRows, ...deleteRows.reverse()];
  if (requests.length == 0) return;
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

When this script is run, the values searched by the column "E" are retrieved from the sheets of etfar, and those values are appended to the destination sheet "Cash". And also, the copied rows of each sheet are removed.
In this script, these processes can be run by one API call.

Note:

When I saw your script, from var check = data[i][4] // ith row, 3rd column, I thought that data[i][4] is the coumn "E". But you say 3rd column. It's the column "C". If you want to search the value of "CASH" from the column "C", please modify getRange("E2:E" srcSheet.getLastRow()) to getRange("C2:C" srcSheet.getLastRow()).

Modified script:

Note:

References: