CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Cesar Saenz
Cesar Saenz

Reputation: 11

Google Sheet program doesn't work when it does

Yeah, so my Google Sheet program doesn't work despite the fact that it actually does. You see, my program is that it creates a custom menu with a custom function which then actives that function when I click on it. I was able to get both my custom menu and custom function to work but after a while, it just up and said that it doesn't work. Specifically, I get a

Exception: Cannot call SpreadsheetApp.getUi() from this context

for createEmptyMenu and

TypeError: Cannot read property 'getRange' of undefined"

for settingA. When I create a new project and copy and paste my work, the project and program works just fine. So why does my program suddenly not work after a while? I also have an onEdit trigger if that helps.

function createEmptyMenu() {
   var menu = SpreadsheetApp.getUi().createMenu("⚙️ Admin Settings");
   menu.addItem("Seperate Rows", "settingA");
   menu.addToUi();
}

function settingA() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssLength = ss.getSheets().length;
  
  for (j = 2; j <= ssLength; j++) {
    var sheet = ss.getSheets()[j];
    var num = sheet.getRange("A1:A").getNumRows();
.........

Upvotes: 0

Views: 47

Answers (1)

Cooper
Cooper

Reputation: 64062

I would do it this way:

function settingA() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets().forEach((sh,i)=>{
    let num = getColumnHeight(1,sh,ss);
  })  
}

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  const 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;
}

ranges like getRange('A1:A') tend to return nulls between lastRow and maxRows

Upvotes: 1

Related Questions