Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Open Google Docs Spreadsheet by name

I have a situation where a script is taking input data and sending it to a spreadsheet. After a while, this spreadsheet becomes too big.

Right now we have to manually move the items from the the primary spreadsheet to a new one. The reason is that not everyone is familiar with the code and are willing to change the ID in the code.

I would like to know if there is a way to open the spreadsheet by name. If not, is there a better way of achieving what we need (described above)

like image 918
user1807201 Avatar asked Jan 08 '13 18:01

user1807201


2 Answers

The DocsList service used in one of the answers no longer functions as it has been depreciated. I updated my scripts to look more like the following.

// Open the file
  var FileIterator = DriveApp.getFilesByName(FileNameString);
  while (FileIterator.hasNext())
  {
    var file = FileIterator.next();
    if (file.getName() == FileNameString)
    {
      var Sheet = SpreadsheetApp.open(file);
      var fileID = file.getId();
    }    
  }

The replacement for DocsList is DriveApp https://developers.google.com/apps-script/reference/drive/drive-app

like image 70
Dan Avatar answered Oct 25 '22 19:10

Dan


Update: DocsList has now been sunset. Use DriveApp.getFilesByName(name) instead.


David has provided some good code for shuffling your data around. If all you really did need was just to open a spreadsheet by name then this will do the trick:

function getSpreadsheetByName(filename) {
  var files = DocsList.find(filename);

  for(var i in files)
  {
    if(files[i].getName() == filename)
    {
      // open - undocumented function
      return SpreadsheetApp.open(files[i]);
      // openById - documented but more verbose
      // return SpreadsheetApp.openById(files[i].getId());
    }
  }
  return null;
}
like image 38
DDD Avatar answered Oct 25 '22 19:10

DDD