TypeError: Cannot find function getDataRange in object

Question

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);
}

Diego · Accepted Answer · 2018-03-30 18:25:29Z

3

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.

answered Mar 30, 2018 at 18:25

Diego

9,5812 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stan Law Over a year ago

Perfect, got me past that problem, but now its throwing up another, more work to be done yet I think.

James D · Accepted Answer · 2018-03-30 18:22:38Z

1

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');

answered Mar 30, 2018 at 18:22

James D

3,1501 gold badge14 silver badges22 bronze badges

2 Comments

Stan Law Over a year ago

Hi Thanks for the reply, My only problem with that is there are always new sheets being added by numerous users each day, which means I would have to change the script each day to add and remove sheet names from the script. What I am trying to achieve is to ask for a list of sheets, and then search the sheets for duplicate values on another workbook and then delete from the first one.

James D Over a year ago

Sorry, I completely missed the 'entire' part of your OP!

Collectives™ on Stack Overflow

TypeError: Cannot find function getDataRange in object

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related