Google API/Python: Delete old files in Google Drive by filename in local folder, upload new filenames from local folder

Question

I have a problem witch was hard to write in the title. I have this script with a lot of help from @Tanaike . This script is doing basically two things:

1. Deletes files from Google Drive folder by filenames which are in local folder CSVtoGD (using spreadsheet ID's)

then:

2. Upload list of CSV from local folder "CSVtoGD" to Google Drive folder

I have a big problem now and can not work it out. The script is deleting old files in google drive when there are the same filenames in CSVtoGD. When I add new file to local folder CSVtoGD, there is a error "list index out of range" and I got printed "No files found" like in the script. I was trying to make some modification but it was blind shoots. What I want this script to do is to delete from Google Drive folder ONLY files which are in local CSVtoGD folder and work on with rest of the files in CSVtoGD (just upload them). Anyone have some answer to that? Thank you :)

import gspread
import os
from googleapiclient.discovery import build

gc = gspread.oauth(credentials_filename='/users/user/credentials.json')

service = build("drive", "v3", credentials=gc.auth)


def getSpreadsheetId(filename):
    q = "name='" + filename + "' and mimeType='application/vnd.google-apps.spreadsheet' and trashed=false"
    res = service.files().list(q=q, fields="files(id)", corpora="allDrives", includeItemsFromAllDrives=True, supportsAllDrives=True).execute()
    items = res.get("files", [])
    if not items:
        print("No files found.")
        exit()
    return items[0]["id"]


os.chdir('/users/user/CSVtoGD2')

files = os.listdir()


for filename in files:
    fname = filename.split(".")
    if fname[1] == "csv":
      folder_id = '1z_pUvZyt5AoTNy-aKCKLmlNjdR2OPo'
      oldSpreadsheetId = getSpreadsheetId(fname[0])
      #print(oldSpreadsheetId)
      sh = gc.del_spreadsheet(oldSpreadsheetId)
**# IF there are the same filenames in CSVtoGD folder on my Mac 
#and the same filenames on Google Drive folder, 
#those lines works well. 
#Problem is when there are new files in CSVtoGD local folder on Mac.**
      sh = gc.create(fname[0], folder_id)
      content = open(filename, "r").read().encode("utf-8")
      gc.import_csv(sh.id, content)

Answer 1

I believe your goal is as follows.

• For example, when sample.csv is existing on your local PC and a Spreadsheet of sample is existing in your Google Drive, you want to delete the Spreadsheet of sample from your Google Drive.
• When sample1.csv is existing on your local PC and the Spreadsheet of sample1 is NOT existing in your Google Drive, you want to upload sample1.csv to Google Drive.

In this case, how about the following modification?

Modified script:

import gspread
import os
from googleapiclient.discovery import build
from googleapiclient.http import MediaFileUpload

gc = gspread.oauth(credentials_filename='/users/user/credentials.json')
service = build("drive", "v3", credentials=gc.auth)
folder_id = '1z_pUvZyt5AoTNy-aKCKLmlNjdR2OPo'  # Please set the folder ID you want to upload the file.


def getSpreadsheetId(filename, filePath):
    q = "name='" + filename + "' and mimeType='application/vnd.google-apps.spreadsheet' and trashed=false"
    res = service.files().list(q=q, fields="files(id)", corpora="allDrives", includeItemsFromAllDrives=True, supportsAllDrives=True).execute()
    items = res.get("files", [])
    if not items:
        print("No files found.")
        
        file_metadata = {
            "name": filename,
            "parents": [folder_id],
            "mimeType": "application/vnd.google-apps.spreadsheet",
        }
        media = MediaFileUpload(filePath + "/" + filename + ".csv")
        file = service.files().create(body=file_metadata, media_body=media, fields="id").execute()
        id = file.get("id")
        print("File was uploaded. The file ID is " + id)
        exit()

    return items[0]["id"]


filePath = '/users/user/CSVtoGD2'
os.chdir(filePath)

files = os.listdir()

for filename in files:
    fname = filename.split(".")
    if fname[1] == "csv":
        oldSpreadsheetId = getSpreadsheetId(fname[0], filePath)
        print(oldSpreadsheetId)
        sh = gc.del_spreadsheet(oldSpreadsheetId)
        sh = gc.create(fname[0], folder_id)
        content = open(filename, "r").read().encode("utf-8")
        gc.import_csv(sh.id, content)

• When this script is run, the above flow is run.

Note:

• In this modification, the CSV file is uploaded as a Google Spreadsheet. From your question, I thought that this might be your expected result. But, if you want to upload the CSV file as the CSV file, please remove "mimeType": "application/vnd.google-apps.spreadsheet", from file_metadata.

• If an error related to the scope, please add the scope of https://www.googleapis.com/auth/drive and authorize the scopes again and test it again.

Reference:

• Upload file data