For loop over a Google Sheets Range fails to change file owner

Question

I need to transfer ownership of thousands of files I own to someone else with editing access, but I've learned that Google Drive requires you to do this one file at a time. With intermediate but growing JS experience I decided to write a script to change the owner from a list of file IDs in a spreadsheet, however I've run into trouble and I have no idea why. The debugger just runs through the script and everything looks sound for me. I'd greatly appreciate any help.

function changeOwner() {
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/********/').getSheetByName('Sheet1');
  var range = ss.getRange(1,1,2211);
  for (i = 0; i < range.length; i++){
    var file = range[i][0].getValue();
    var fileid = DriveApp.getFileById(file);
    fileid.setOwner('******@gmail.com');
  }
}

Have you tried to run this code for a single record first? Does it work? — Darpan Sanghavi, Apr 19 '18 at 11:47
Yes, it does work when I assign a single file ID to the fileid variable. — rylaughlin, Apr 19 '18 at 11:51
Okay, could you please try adding this ID in Google Sheet, and perform this action on single record? — Darpan Sanghavi, Apr 19 '18 at 11:52
I've removed the loop and assigned a file ID to both the file and fileid variable and both worked, so it looks like my loop is the problem. — rylaughlin, Apr 19 '18 at 11:55
It looks like it, that's why I wanted to check by putting this ID in excel sheet and then run the program. Instead of ss.getRange() try with ss.getDataRange().getValues() — Darpan Sanghavi, Apr 19 '18 at 12:00
Possible duplicate of [Google Spreadsheets: Iterate over range, append string to each](https://stackoverflow.com/questions/13605213/google-spreadsheets-iterate-over-range-append-string-to-each) — tehhowch, Apr 19 '18 at 14:09

tehhowch · Answer 1 · 2018-04-19T16:37:47.663

Your issue is that the Range class had no length property. Instead, perform a getValues() call on the range to efficiently create a JavaScript array, and iterate on it:

function changeOwner() {
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/********/').getSheetByName('Sheet1');
  var values = ss.getRange(1, 1, 2211).getValues();
  for (i = 0; i < values.length; ++i){
    var fileid = value[i][0];
    var file = DriveApp.getFileById(fileid);
    file.setOwner('******@gmail.com');
  }
}

There are other improvements you can make, such as:

dynamic range read (rather than the fixed assumption of 2211 rows of data)
writing to a second array to keep track of which files you have yet to process
checks for if the file exists
checks for if the file is actually owned by you
checks for execution time remaining, to allow serializing any updates/tracking you do that prevents the next execution from repeating what you just did

and so on. I'll let you research those topics, and the methods available in Apps Script documentation for the DriveApp, SpreadsheetApp, Sheet, and Range classes. Note that you also have all features of Javascript 1.6 available, so many of the things you find on a Javascript Developer Reference (like MDN) are also available to you.

score 1 · Answer 2 · answered Apr 19 '18 at 12:07

Tested below code working fine with IDs,

function myFunction() {
  var spreadsheet = SpreadsheetApp.openById('ID');
var range = spreadsheet.getDataRange().getValues();
for (i = 0; i < range.length; i++){
    var file = range[i][0].toString();
    var fileid = DriveApp.getFileById(file);
    fileid.setOwner('***@gmail.com');
}
}

For loop over a Google Sheets Range fails to change file owner

2 Answers2