I'm relatively new to Apps Scripts and am still refreshing my basic Java coding and Sheets formulas so please bear with me.

I have over a thousand invoices with varying number of rows across multiple spreadsheets, but each invoice being one tab within each sheet. I'm trying to figure out a way to spit out the word "End" in column A (for an automated process a third-party is building out) in the corresponding row of the last non-empty cell in Column F (i.e. last non-empty cell is Column F is F23, I want A23 to say "End"). Since the last non-empty cell in column F changes rows between each invoice, it's prevented me from using an Sheets formula, as I haven't figured out a way to only populate "End" in the same row as the actual last non-empty cell.

I've tried using the IF(OR(ISNUMBER(SEARCH(... formula, but I'm not quite sure how to link this with only populating text relative to the last non-empty.

Any help is greatly appreciated.

Thanks

CodePudding user response：

Set Column A to End

You need to provide the folder id in the second line and then hope that the script finishes in about 6 minutes.

This is not a Custom Script. It must be run from a menu or script editor

function setColumnAToEnd() {
  const folder = DriveApp.getFolderById("folderId");
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  while(files.hasNext()) {
    let file = files.next();
    let ss = SpreadsheetApp.openById(file.getId());
    ss.getSheets().forEach(sh => {
      let lr = getColumnHeight(6,sh,ss);
      sh.getRange(lr,1).setValue("End");
    })
  }
}

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()){ rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
  let s = 0;
  for (let i = 0; i < rcA.length; i  ) {
    if (rcA[i].toString().length == 0) {
      s  ;
    } else {
      break;
    }
  }
  return rcA.length - s;
}

CodePudding user response：

There's probably better ways to do this, but you can use this formula to find the last row in column F. =MAX(FILTER(ROW(F:F),NOT(ISBLANK(F:F))))

If you wanted to automate, I suppose you could do something weird like this... If you run this on your sheet, it should work.

Updated to work on entire workbook.

function fixAllSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  allSheets.forEach(enterSomeEnd_)
}

function enterSomeEnd_(theSheet) {
  var someRandomCell = theSheet.getRange(theSheet.getMaxRows(), 1, 1, 1);
  someRandomCell.setFormula('=MAX(FILTER(ROW(F:F),NOT(ISBLANK(F:F))))');
  var theRow = someRandomCell.getValue();
  theSheet.getRange(theRow, 1).setValue("end");
  someRandomCell.clearContent();
}

CodePudding user response：

Is there a way to tie in the script you wrote into the format function I currently have set up into the function SKUInvoice2 below:

Edited since combining the two, but I changed "theSheet" to "spreadsheet" just to match the macro code that apps script pre-populated and everything working perfectly! Thank you both.

    function SKUInvoiceall() {    
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheets();
        for (i=0;i<spreadsheet.length;i  ){ 
            var sheet = spreadsheet[i];
           SKUInvoice2(sheet);
}
function SKUInvoice2(spreadsheet) {
  var spreadsheet = spreadsheet
  spreadsheet.getRange('B:B').activate();
  spreadsheet.insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
  spreadsheet.getActiveRange().offset(0, 0, spreadsheet.getActiveRange().getNumRows(), 1).activate();
  spreadsheet.getRange('B10').activate()
  .setFormula('=IF(OR(ISNUMBER(SEARCH("NAAT",C10))),"1163344",IF(OR(ISNUMBER(SEARCH("Molecular",C10))),"1163344",IF(OR(ISNUMBER(SEARCH("Rapid Covid",C10))),"1163344",IF(OR(ISNUMBER(SEARCH(" PCR",C10))),"7105",IF(OR(ISNUMBER(SEARCH("RT-PCR",C10))),"1166916", IF(OR(ISNUMBER(SEARCH("Treatment",C10))),"SKU", ""))))))');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('B10:B48'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('A25').activate();
  var someRandomCell = spreadsheet.getRange(spreadsheet.getMaxRows(), 1, 1, 1);
  someRandomCell.setFormula('=MAX(FILTER(ROW(F:F),NOT(ISBLANK(F:F))))');
  var theRow = someRandomCell.getValue();
  spreadsheet.getRange(theRow, 1).setValue("END");
  someRandomCell.clearContent();
}