Home > Software design >  I just want my Goggle Sheets loop to stop when a specific cell = Total
I just want my Goggle Sheets loop to stop when a specific cell = Total

Time:04-24

function TRANSPOSE1() {
  var spreadsheet = SpreadsheetApp.getActive();
  do {
    spreadsheet.getRange('F2').activate();
    spreadsheet.getCurrentCell().setFormula('=IFERROR(TRANSPOSE(D18:D31),FALSE)');
    var currentCell = spreadsheet.getCurrentCell();
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
    currentCell.activateAsCurrentCell();
    spreadsheet.getRange('F5').activate();
    spreadsheet.getRange('F2:S2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.getRange('F5:S5').insertCells(SpreadsheetApp.Dimension.ROWS);
    spreadsheet.getRange('D18:D31').activate();
    spreadsheet.getRange('D18:D31').deleteCells(SpreadsheetApp.Dimension.ROWS);
    spreadsheet.getRange('F3').activate();
    var cc_total = spreadsheet.getCurrentCell()
    cc_total.activate();
  } while (cc_total != "Total");
};

I am currently using Google Sheets to run a USFL Fantasy football league and this is driving me insane. Basically I have a IMPORTXML for a website I get my stats, but the stats all come in vertical, so I made this code above to TRANSPOSE each individual player in a row. Currently, I have to run my code for each player but I want it to be automated to stop when the word "Total" is in the top cell, because I don't need the total stats in my rows. I really am not understanding why when I set WHILE to the "cc_total" variable to "!=" (not equal) "Total", it keeps running when the cell DOES == "Total". What am I not getting here?

CodePudding user response:

Currently cc_total is getting a range not a value

So your loop is not working the way you think it is

Replace var cc_total = spreadsheet.getCurrentCell() with var cc_total = spreadsheet.getCurrentCell().getDisplayValue() and replace cc_total.activate(); with spreadsheet.getCurrentCell().activate()

  • Related