SpreadsheetApp.getActiveSpreadsheet() returning null

Question

I need a quick function to loop through the rows of one of my sheets so I constructed this:

function getTotalsOf(name, type) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
  var data = sheet.getDataRange().getValues();
  var total = 0.00;
  for (var i = 0; i < data.length; i++) {
    if (data[i][0]== "") break;
    if (data[i][2] == type) {
      var temp = (Number(data[i][4]) + Number(data[i][5]) + Number(data[i][6])) * data [i][3];
        total = Number(total) + total;
        }
  }
  return total;
}

SpreadsheetApp.getActiveSpreadsheet() seems to return null when I try to execute the function. It also returns null when I try to get the spreadsheet by id. Am I missing something?

EDIT: I retried using my spreadsheet id (https://docs.google.com/spreadsheets/d/STRINGFROMHERE/edit#gid=1235572150) with the same results.

function getTotalsOf(name, type) {
  var sheet = SpreadsheetApp.openById("**STRINGFROMURL**").getSheetByName(name);
  var data = sheet.getDataRange().getValues();
  var total = 0.00;
  for (var i = 0; i < data.length; i++) {
    if (data[i][0]== "") break;
    if (data[i][2] == type) {
      var temp = (Number(data[i][4]) + Number(data[i][5]) + Number(data[i][6])) * data [i][3];
        total = Number(total) + temp;
        }
  }
  return total;
}

Works fine. 2 things I can point out that are potentially wrong: 1) the script is standalone (that means the script file is in your Google Drive and not access through the spreadsheet); 2) you are trying to run getTotalsOf directly without providing the sheet name. For example if I have a sheet called Sheet1 I must run something like getTotalsOf('Sheet1', 'someType') from another function — Vytautas
Here is an example reference from my sheet: =getTotalsOf("Revenue","Misc") I always give it a sheet name and a type. — Mason Adkins
After further testing, it is getting the proper spreadsheet by id. What it appears I am running into is when I press the debug button, it runs without a name or a type and that is what causes the error. How can I tell the debug what parameters to use? — Mason Adkins
You will need to debug with a debugFunction() which only needs to contain getTotalsOf('sheet1','box') (obviously write the correct sheet name and type you wish to debug with). Running getTotalsOf() from the script editor or using debug will always leave name and type as undefined — Vytautas

noogui noogui · Accepted Answer · 2017-07-07T13:49:34

I think you just missed a few concepts with regard to looping through spreadsheets. Hope this demo will help and get you what you want. Always remember that rows and cols in spreadsheets starts with 1,1 not 0,0.

  function loopThroughCells() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var data = sheet.getDataRange().getValues();
      Logger.log(data[0].length);

  for(var i=0; i< data.length; i++){
    for(var j=0; j<data[i].length; j++){
      if(data[i][j] == "HELIOS"){
           if (data[i][j]== "") break;
           Logger.log("HELIOS data found in range "+ (i+1) + " "+(j+1)); 
      }
    }
  }

Log

[17-07-07 21:45:17:157 HKT] HELIOS data found in range 3 3

SpreadsheetApp.getActiveSpreadsheet() returning null

1 Answers