Find and Replace function multiple documents in Shared Drives

Question

The issue : I have multiple documents ( specifically, Google Sheets ) in multiple Shared Drives that need the same single word replaced. Some searching around this forum found me the following code, but it doesn't seem to be working. I'm not getting any error messages, but when I go check the files nothing has been changed. I checked and made sure I put in my search string and replacement string correctly.

Do I need to do something different to get it to check files in Shared Drives? I tried turning on the Drive API in Advanced Google Services, but that just gave me an error in line 6 ( var doc = DocumentApp.openById(file.getId()); )

Here's the code I was using :

     function myFunction() {
  var files = DriveApp.getFiles();   // Note: this gets *every* file in your Google Drive
  while (files.hasNext()) {
    var file = files.next();
    Logger.log(file.getName());
    var doc = DocumentApp.openById(file.getId());
    doc.replaceText("My search string or regex", "My replacement string");
  }
  Logger.log("Done")
}

Answer 1

DriveApp.getFiles() returns only the files on "My Drive". In order to be able to get the files in a Shared Drive (formerly Team Drive) you have to enable the Drive Advanced Service and, instead of

var files = DriveApp.getFiles();

use something like

var files = Drive.Files.list(options);

Related

• Finding shared date of a file in GAS
• Google Apps Script TeamDrive DriveApp.searchFolders and DriveApp.searchFiles does not return any results
• "Shared Drive" support in Google Apps Script

Answer 2

In order to access the shared drives, you need to activate the Drive API in the Advanced Google Services, but in order to access it, you will have to use Drive and not DriveApp.

If you want to retrieve the shared drives specifically and look for files with a certain name, you can try this:

Code

// Copyright 2020 Google LLC.
// SPDX-License-Identifier: Apache-2.0

function retrieveFiles() {
   var allDrives = Drive.Drives.list().items;
   let fileName = 'FILE_NAME';
   for (let i = 0; i < allDrives.length; i++) {
      var driveId = allDrives[i].id;
      console.log(id)
      var optionalArgs = {
         'driveId': driveId,
         'includeItemsFromAllDrives': true,
         'corpora': 'drive',
         'supportsAllDrives': true,
         'q': "title=" + "'" + fileName + "'"
      };
      var filesReturned = Drive.Files.list(optionalArgs).items;
      for (let j = 0; j < filesReturned.length; j++) {
         var fileId = filesReturned[j].id;
         var sheet = SpreadsheetApp.openById(fileId);
         //sheet manipulation
      }
   }
}

Explanation

The above code retrieves all the shared drives by making use of the Drives:list method and then for each shared drive, searches for all the files which are named FILE_NAME using Files:list. Then, it accesses each file by using SpreadsheetApp.

Note

Please bear in mind that the code can be customized in order to fit your needs and requirements specifically.

Reference

• Drive API Drives:list;

• Drive API Files:list;

• Advanced Google Services Apps Script;

• SpreadsheetApp Class Apps Script.