CODEWITHSUNDEEP
javascriptgoogle-apps-script
Alexander Starbuck
Alexander Starbuck

Reputation: 1159

Google Sheets script - "Cannot read property of undefined" (when it's not)

I am trying to de-construct a Google-Team's script for getting a report from AdWords and writing it into Google Sheets spreadsheet. I am testing it in Apps editor and getting the "Cannot read of undefined..." error.

I currently have two .gs files in my project:

1. Function definition ("class"), empty_row.gs file:

function SpreadsheetAccess(spreadsheetUrl, sheetName) {
this.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
this.sheet = spreadsheet.getSheetByName(sheetName);

this.findEmptyRow = function(minRow, column) {
    var values = this.sheet.getRange(minRow, column, this.sheet.getMaxRows(), 1).getValues();

    for (var i = 0; i < values.length; i++) {
        if(!values[i][0]) {
            return i + minRow;
        }
    }
    return -1;
    };

this.addRows = function(howMany){
    this.sheet.insertRowsAfter(this.sheet.getMaxRows(), howMany);
};

this.writeRows = function(){
    this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).setValue(rows);
};
}

2. and a main.gsfile where I instantiate the object:

var spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/someurl/edit#gid=1540200210';
var sheetName = 'baba';

function main() {
  var object = SpreadsheetAccess(spreadsheetUrl, sheetName);
  var emptyRow = object.findEmptyRow(1, 1);

  Logger.log("The empty row is: %s", emptyRow);  
}

When I run main{} I am getting this:

TypeError: Cannot call method "findEmptyRow" of undefined. (line 6, file "main")

Why is my object undefined? I have defined:

What gives? thanks!

Upvotes: 0

Views: 8273

Answers (1)

Douglas Gaskell
Douglas Gaskell

Reputation: 10030

You shouldn't be trying to modify objects you do not own with the use of this. Especially in Google Apps Script. AFAIK this actually refers to the object that contains all the services code for Apps Script itself.

Create your own objects and pass those around instead of changing the object with global scope, while it will technically work to stuff everything into that object and reference it, it's discouraged.

I was incorrect in my reasoning. The problem I posted still stands though.

The Problem:

object is undefined because you are not returning a value from SpreadsheetAccess(). Nothing has been defined as nothing has been returned.

Solution 1

Here is your code with two changes. Newing up the SpreadsheetAccess function, and changing spreadsheet.getSheetByName(sheetName); to this.spreadsheet.getSheetByName(sheetName);

var spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/someurl/edit#gid=1540200210';
var sheetName = 'baba';

function main() {
  var object = new SpreadsheetAccess(spreadsheetUrl, sheetName);
  var emptyRow = object.findEmptyRow(1, 1);

  Logger.log("The empty row is: %s", emptyRow);  
}

function SpreadsheetAccess(spreadsheetUrl, sheetName) {
  this.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  this.sheet = this.spreadsheet.getSheetByName(sheetName);

  this.findEmptyRow = function(minRow, column) {
    var values = this.sheet.getRange(minRow, column, this.sheet.getMaxRows(), 1).getValues();

    for (var i = 0; i < values.length; i++) {
      if(!values[i][0]) {
        return i + minRow;
      }
    }
    return -1;
  };

  this.addRows = function(howMany){
    this.sheet.insertRowsAfter(this.sheet.getMaxRows(), howMany);
  };

  this.writeRows = function(){
    this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).setValue(rows);
  };
}

Solution 2 Here is my modified code, this code works, I already tested it with my own spreadsheet:

var spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/someurl/edit#gid=1540200210';
var sheetName = 'baba';

function main() {
  var myObject = SpreadsheetAccess(spreadsheetUrl, sheetName);
  var emptyRow = myObject.findEmptyRow(1, 1);

  Logger.log("The empty row is: %s", emptyRow);  
}

function SpreadsheetAccess(spreadsheetUrl, sheetName) {
  var myObject = {};

  myObject.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  myObject.sheet = myObject.spreadsheet.getSheetByName(sheetName); 

  myObject.findEmptyRow = function(minRow, column) {
    var values = myObject.sheet.getRange(minRow, column, myObject.sheet.getMaxRows(), 1).getValues();

    for (var i = 0; i < values.length; i++) {
      if(!values[i][0]) {
        return i + minRow;
      }
    }
    return -1;
  };

  myObject.addRows = function(howMany){
    myObject.sheet.insertRowsAfter(myObject.sheet.getMaxRows(), howMany);
  };

  myObject.writeRows = function(startRow, startColumn, rows){
    myObject.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).setValue(rows);
  };
  return myObject;
}

Upvotes: 1

Related Questions