CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Stan Law
Stan Law

Reputation: 21

TypeError: Cannot find function getDataRange in object

I am trying to create a script that will search the entire of workbook "A" and workbook "B" for a duplicate value, and then delete the row containing the duplicate from its location in workbook "A".

However when running the script I get error "TypeError: Cannot find function getDataRange in object" back at me and I cannot figure out why.

The script I am working on is below:

   function deleteRowInSpreadsheet1() { 
var s1 =SpreadsheetApp.openById("1pxWr3jOYZbcwlFR7igpSWa9BKCa2tTeliE8FwFCRTcQ").getSheets();
var s2 = SpreadsheetApp.openById("18jLxZlge_UFkdTjlO6opuDk1VKeezhEPLe9B7n1OfQs").getSheets(); 
var values1 = s1.getDataRange().getValues();
var values2 = s2.getDataRange().getValues();
var resultArray = [];
for(var n in values2){
var keep = true
for(var p in values1){
  if( values1[n][0] == values1[p][0] && values2[n][1] == values1[p][1]){
    keep=false ; break ;
  }
}
if(keep){ resultArray.push(values2[n])};
}
s1.clear()
s1.getRange(1,1,resultArray.length,resultArray[0].length).setValues(resultArray);
}

Upvotes: 1

Views: 3107

Answers (2)

Diego
Diego

Reputation: 9571

You're calling getSheets(), which returns an Array of sheets. So you'll need to loop through each sheet and then call getDataRange().

As an example:

var s1 =SpreadsheetApp.openById("1pxWr3jOYZbcwlFR7igpSWa9BKCa2tTeliE8FwFCRTcQ").getSheets();
for (var i=0; i<s1.length; i++) {
  var values = s1[i].getDataRange().getValues();
}

Then, of course, use those values where you need them.

Upvotes: 3

James D
James D

Reputation: 3142

In the lines 

var s1 =SpreadsheetApp.openById("RTcQ").getSheets();
var s2 = SpreadsheetApp.openById("7fQs").getSheets();

You use the .getSheets() method which is returning an object containing all the sheets in that spreadsheet. You cannot get the data range of this object.

Instead, you should use .getActiveSheet() or .getSheetByName()

var s1 =SpreadsheetApp.openById("RTcQ").getSheetByName('Sheet1');
var s2 = SpreadsheetApp.openById("7fQs").getSheetByName('Sheet2');

Upvotes: 1

Related Questions