CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
Austin Elliott
Austin Elliott

Reputation: 41

Google Sheets Script - Cannot read property 1.0?

I am trying to enter in data via a script, so that it finds the first empty row, and in that row the first empty column and enter the data into that column. For each row there will be several columns to insert information into, and it is done in separate requests which I realize is another problem that I have to solve, but I haven't gotten there yet.

var answer = e.parameter["value"]
  var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
  var sheet = doc.getSheetByName(e.parameter["sheet"]);
  var row = sheet.getLastRow();
  var column = sheet.getLastColumn()
  var range = sheet.getRange(row, 1, 1, column);
  var data = range.getValues();

  for(var i = 1; i <= column; i++){
    if (data[1][i] == "" || data[1][i] == " "){
      sheet.getRange(1, i).setValue(answer);
      break;
    }
  }

I am getting this error when it is run: "message":"Cannot read property \"1.0\" from undefined.","name":"TypeError","fileName":"Code (Database Macro)","lineNumber":107,"stack":"\tat Code (Database Macro):107 (doPost

EDITED CODE:

for(var i = 1; i <= column; i++){
    if (data[i] == ""){
      sheet.getRange(row, i).setValue(answer);
      break;
    }
  }

Upvotes: 1

Views: 1391

Answers (2)

Jack Brown
Jack Brown

Reputation: 5892

Your problem lies here:

Javascript array index starts at 0 not 1 as mentioned by @ZigMandel so modify the below code: 

for(var i = 1; i <= column; i++){
    if (data[1][i] == "" || data[1][i] == " "){ // This i believe is your line 107
      sheet.getRange(1, i).setValue(answer);
      break;
    }
  }

to this:

for(var i = 0; i < column; i++){
        if (data[0][i] == "" || data[0][i] == " "){
          sheet.getRange(1, i).setValue(answer);
          break;
        }
      }

The error you are get is most likely because of this statement data[1][i]. Since array index starts at 0 and you got one row from your this code:

 var range = sheet.getRange(row, 1, 1, column);
  var data = range.getValues();

There is no index 1, hence data[1] is undefined.

Upvotes: 1

Kresimir Pendic
Kresimir Pendic

Reputation: 3614

I'm sharing you sheet where you can see how I find first empty row (cell value) in range A1:A10

https://docs.google.com/spreadsheets/d/12w3Xi2CsOQ7u0i6gB5T7SWhG5_Kkv_04UJOp2ouNpF0/edit?usp=sharing

function myFunction() {
  var aa = SpreadsheetApp.getActiveSheet().getRange("A1:A10").getValues();
  var ui = SpreadsheetApp.getUi();
  var ix = null;
  for( var i = 0 ; i < aa.length; i++){
    if( aa[ i ] == "" ) { ix = i; break; }
  }
  ui.alert( "first empty row is: A" + (ix+1) ); 
}

hth, cheers

Upvotes: 1

Related Questions