rubygoogle-apps-scriptgoogle-cloud-platformgoogle-apps-script-api

Permission issues deleting files with Google Drive API in ruby

I have a Google Apps Script that creates json files from spreadsheet tabs:

function makeJSON() {
  var spreadsheetId = 'someId'; 
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheets = spreadsheet.getSheets();
  var urls = []; // Array to store the URLs of the created files

  for (var i =  1; i < sheets.length; i++) {
    var sheet = sheets[i];
    var data = sheet.getRange(3,  1, sheet.getLastRow() -  2, sheet.getLastColumn()).getValues();
    var headers = sheet.getRange(2,  1,  1, sheet.getLastColumn()).getValues()[0];

    var objects = data.map(function(row) {
    var obj = {};
    for (var j =  0; j < headers.length; j++) {
      //process data
    }
    
    return obj;
    });


     // Convert the array of objects to a JSON string
    var json = JSON.stringify(objects, null,  2);
    
    // Create a blob from the JSON string
    var blob = Utilities.newBlob(json, 'application/json', sheet.getName() + '.json');

    var folder = DriveApp.getFolderById('someId');
    var file = folder.createFile(blob);

    file.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT); //I don't like making these publicly editable but it seems I'm forced to in order to be able to download/delete them via ruby script
    
    // Get the file ID
    var fileId = file.getId();
    
    // Construct the direct download URL
    var directDownloadUrl = "https://drive.google.com/uc?export=download&id=" + fileId;
    
    // Add the direct download URL to the array
    urls.push(directDownloadUrl);
    
  }
  return urls;
}

function doGet() {
  var urls = makeJSON(); // Call the function to generate JSON files and get their URLs
  return ContentService.createTextOutput(JSON.stringify({urls: urls})).setMimeType(ContentService.MimeType.JSON);
}

I have a ruby script that downloads all these files from Google Drive. That part works. But I also want to delete them after downloading:

drive_service = Google::Apis::DriveV3::DriveService.new
scope = 'https://www.googleapis.com/auth/drive'

authorizer = Google::Auth::ServiceAccountCredentials.make_creds(
  json_key_io: File.open('google_client.json'),
  scope: scope
)

authorizer.fetch_access_token!

# Set the authorization for the Drive API client
drive_service.authorization = authorizer

# URL of your deployed web app that returns JSON file URLs
web_app_url = 'https://script.google.com/macros/s/digits/exec'

# Fetch the JSON data from the web app
response = URI.open(web_app_url).read
json_data = JSON.parse(response)

# Directory to save the JSON files
save_directory = 'downloads'

# Ensure the directory exists
FileUtils.mkdir_p(save_directory) unless File.directory?(save_directory)

json_data['urls'].each do |url|
  # Extract the file ID from the URL
  file_id = url.split('id=')[1]
   
  # Fetch the file's metadata using the Google Drive API
  file_metadata = drive_service.get_file(file_id, fields: 'name')
   
  # Extract the file name
  file_name = file_metadata.name
   
  # Construct the direct download URL
  direct_download_url = "https://drive.google.com/uc?export=download&id=#{file_id}"
   
  # Construct the full path
  file_path = File.join(save_directory, file_name)
   
  # Download the file
  URI.open(direct_download_url) do |source|
    File.open(file_path, 'wb') do |file|
      file.write(source.read)
    end
  end
  puts "Saved #{file_name} to #{save_directory}"

  # Delete the file from Google Drive
  drive_service.delete_file(file_id) #THIS LINE THROWS THE ERROR
end

Here's the error:

/Users/me/.rvm/gems/ruby-2.7.2/gems/google-apis-core-0.14.0/lib/google/apis/core/http_command.rb:244:in `check_status': insufficientFilePermissions: The user does not have sufficient permissions for this file. (Google::Apis::ClientError)

I think the problem is that in my ruby script credentials are from my service account (servicename@servicename-412394.iam.gserviceaccount.com) and the files on Google Drive are owned by "Me" (myregularemail@gmail.com). But I can't figure out either how to make them owned by my service account in the first place or to run the script with my regular email account credentials. When you create service accounts in the Cloud console it forces you to use *.iam.gserviceaccount.com accounts.

The Google Drive folder is shared with my service account. All the files in it are Owned by "Me" and my service account has Editor permissions on all of them.

So, all that said, how can I delete these files from Google Drive after I download them? The files pile up quickly and I need to keep this directory clean.

Solution

  • In your situation, as a simple approach, how about deleting the file in Web Apps? When this is reflected in your script, how about the following modification?

    By this modification, after the file is downloaded, the file is deleted with Web Apps.

    Google Apps Script:

    Before you use this script, pleaes enable Drive API v3 at Advanced Google services.

    function makeJSON(e) { // Modified
  // Added
  if (e.parameter.deleteFile) {
    Drive.Files.remove(e.parameter.deleteFile); // or Drive.Files.remove(e.parameter.deleteFile, { supportsAllDrives: true });
    return [];
  }

  var spreadsheetId = 'someId'; 
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheets = spreadsheet.getSheets();
  var urls = []; // Array to store the URLs of the created files

  for (var i =  1; i < sheets.length; i++) {
    var sheet = sheets[i];
    var data = sheet.getRange(3,  1, sheet.getLastRow() -  2, sheet.getLastColumn()).getValues();
    var headers = sheet.getRange(2,  1,  1, sheet.getLastColumn()).getValues()[0];

    var objects = data.map(function(row) {
    var obj = {};
    for (var j =  0; j < headers.length; j++) {
      //process data
    }
    
    return obj;
    });


     // Convert the array of objects to a JSON string
    var json = JSON.stringify(objects, null,  2);
    
    // Create a blob from the JSON string
    var blob = Utilities.newBlob(json, 'application/json', sheet.getName() + '.json');

    var folder = DriveApp.getFolderById('someId');
    var file = folder.createFile(blob);

    file.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT); //I don't like making these publicly editable but it seems I'm forced to in order to be able to download/delete them via ruby script
    
    // Get the file ID
    var fileId = file.getId();
    
    // Construct the direct download URL
    var directDownloadUrl = "https://drive.google.com/uc?export=download&id=" + fileId;
    
    // Add the direct download URL to the array
    urls.push(directDownloadUrl);
    
  }
  return urls;
}

function doGet(e) { // Modified
  var urls = makeJSON(e); // Modified
  return ContentService.createTextOutput(JSON.stringify({urls: urls})).setMimeType(ContentService.MimeType.JSON);
}

    Ruby:

    drive_service = Google::Apis::DriveV3::DriveService.new
scope = 'https://www.googleapis.com/auth/drive'

authorizer = Google::Auth::ServiceAccountCredentials.make_creds(
  json_key_io: File.open('google_client.json'),
  scope: scope
)

authorizer.fetch_access_token!

# Set the authorization for the Drive API client
drive_service.authorization = authorizer

# URL of your deployed web app that returns JSON file URLs
web_app_url = 'https://script.google.com/macros/s/digits/exec'

# Fetch the JSON data from the web app
response = URI.open(web_app_url).read
json_data = JSON.parse(response)

# Directory to save the JSON files
save_directory = 'downloads'

# Ensure the directory exists
FileUtils.mkdir_p(save_directory) unless File.directory?(save_directory)

json_data['urls'].each do |url|
  # Extract the file ID from the URL
  file_id = url.split('id=')[1]
   
  # Fetch the file's metadata using the Google Drive API
  file_metadata = drive_service.get_file(file_id, fields: 'name')
   
  # Extract the file name
  file_name = file_metadata.name
   
  # Construct the direct download URL
  direct_download_url = "https://drive.google.com/uc?export=download&id=#{file_id}"
   
  # Construct the full path
  file_path = File.join(save_directory, file_name)
   
  # Download the file
  URI.open(direct_download_url) do |source|
    File.open(file_path, 'wb') do |file|
      file.write(source.read)
    end
  end
  puts "Saved #{file_name} to #{save_directory}"

  # Delete the file from Google Drive

  # removed: drive_service.delete_file(file_id) #THIS LINE THROWS THE ERROR
  URI.open(web_app_url + "?deleteFile=#{file_id}") # Modified

end

    Note: