How can I write a Google Sheet file to the Google Drive file structure on my system

Question

I am trying to write a python script that writes a Postgres table to a datafile and saves it to the system Google Drive (G:). I would like it to be in google sheet format (.gsheet). But it seems like no matter what I try, my Google Drive won't allow a user to write or move a .gsheet in to the G: drive and it wont allow a file within the google drive to be renamed with a .gsheet extension.

Additional info:

1. I can write the table to a file with pretty much any extension (including .gsheet) anywhere else on the file structure (ie: C:)
2. I can write the table to a file with any non-.gsheet extension on the Google Drive (G:)
3. I can rename any extension to .gsheet in any non-Google Drive (ie: C:) location.
4. I cannot move any .gsheet file to the G: drive ('file no longer located at location ...' error). Nor can I...
5. Rename a file in the G: drive with a .gsheet extension ('file too large error').
6. The craziest of all: If I write a .csv file to the Google Drive, visit the Google Drive online, and select 'Open with GSheet', Google creates a new .gsheet file for viewing, and it appears in my local Google Drive as a .gsheet file.

The 'file no longer located at location ...' errors are accompanied by a system pop up prompting me to log in to my google drive, but since I can write a .csv file to my local Google Drive and it appears when I view it in a browser, aren't I already logged in?

Is there anyway to:

1. write a .gsheet file to the G: drive
2. move a .gsheet file to the G: drive
3. rename a file within the G: drive to a .sheet extension?

I assume I am missing something super obvious, because right now it is looking like I will need to implement the Google Drive API to interact with Google directly to write a .gsheet file to the drive, as opposed to writing it to a synced folder with system functions. At this point, I am more likely to write a .csv file to the G: drive and suffer the extra clicks to 'open in gsheet' in the google drive web view.

Code can be posted upon request.

Thanks

Edit: Code as requested:

import psycopg2 as ppg
import time
import os

db conenction paramters omitted

date = time.strftime("%Y%m%d")

save_e_path = 'G:\\My Drive\\estate\\daily_expense_upload\\expenses_{}.gsheet'.format(date)

save_s_path = 'G:\\My Drive\\estate\\daily_schedule_upload\\schedule_{}.gsheet'.format(date)

expense_file = open(save_e_path, 'a')
schedule_file = open(save_s_path, 'a')

conn = ppg.connect("dbname=\'{}\' user=\'{}\' password=\'{}\'".format(db, user, password))
cur = conn.cursor()

cur.execute("select * from estate.expenses;")
expenses = cur.fetchall()

cur.execute("select * from estate.schedule;")
schedules = cur.fetchall()

for i in expenses:
    expense_file.write('\n')
    for j in i:
        expense_file.write('"' + str(j) + '"' + ',')

for i in schedules:
    schedule_file.write('\n')
    for j in i:
        schedule_file.write('"' + str(j) + '"' + ',')

Answer 1

As may be apparent from the comments, you can't just write a .gsheet file to GDrive like that. For starters, it's not even writing it in the format of a GSheet file - not that the actual format is even known (iirc). And clearly, the app is not allowing you to create arbitrary files with a .gsheet extension.

It looks like you're basically writing out your data as a text file, and quite badly. Consider doing it as a CSV dump and then upload to Google Drive, and enable conversion.

See this answer - How to save results of postgresql to csv/excel file using psycopg2? - for a better way to save each of your tables to a CSV file. Btw, do one table at a time instead of loading both into memory with cur.fetchall().

The craziest of all: If I write a .csv file to the Google Drive, visit the Google Drive online, and select 'Open with GSheet', Google creates a new .gsheet file for viewing, and it appears in my local Google Drive as a .gsheet file.

Yes, this would be a way to have your data as a .gsheet file.

To take advantage of the automatic conversion to gsheet without having to use the full GSheet API, read this in the docs - requires using the GDrive API and as an upload instead of copying to local path:

To convert a file to a specific Google Workspace file type, specify the Google Workspace mimeType when creating the file. The following shows how to convert a CSV file to a Google Workspace sheet:

file_metadata = {
    'name': 'My Report',
    'mimeType': 'application/vnd.google-apps.spreadsheet'
}
media = MediaFileUpload('files/report.csv',
                        mimetype='text/csv',
                        resumable=True)
file = drive_service.files().create(body=file_metadata,
                                    media_body=media,
                                    fields='id').execute()
print 'File ID: %s' % file.get('id')