- You want to convert Google Spreadsheet to a PDF format, and create it as a file on the same folder of the Google Spreadsheet.
- In this case, the PDF file is created on Google Drive.
- You want to achieve this using pydrive with Python.
Issue and workaround:
Unfortunately, the Google Spreadsheet cannot be directly converted and export to Google Drive using Google API. In this case, it is required to use the workarounds. In this answer, I would like to propose the following 2 workarounds.
Pattern 1:
In this pattern, your goal is achieved using pydrive. In this case, the Spreadsheet is downloaded to the local PC as a PDF file, and upload the PDF file to the same folder of the Spreadsheet on Google Drive.
Sample script:
spreadsheet_id = '###' # Please set the Spreadsheet ID.
pdf_filename = 'sample.pdf'
drive = GoogleDrive(gauth)
# Download Spreadsheet as PDF file.
dl_file = drive.CreateFile({'id': spreadsheet_id})
dl_file.GetContentFile(pdf_filename, mimetype='application/pdf')
# Get parent folder ID of the Spreadsheet.
url = 'https://www.googleapis.com/drive/v3/files/' + spreadsheet_id + '?fields=parents'
headers = {'Authorization': 'Bearer ' + drive.auth.credentials.token_response["access_token"]}
res = requests.get(url, headers=headers)
# Upload the PDF file to the same folder of Spreadsheet.
up_file = drive.CreateFile({'parents': [{'id': res.json()['parents'][0]}]})
up_file.SetContentFile(pdf_filename)
up_file.Upload()
print('title: %s, mimeType: %s' % (up_file['title'], up_file['mimeType']))
- In order to retrieve the parent folder ID of Spreadsheet, I used the method of files.get in Drive API using
requests.
Pattern 2:
In this pattern, your goal is achieved using Web Apps created by Google Apps Script. In this case, using Web Apps, the Spreadsheet is converted to a PDF format and create a file to the same folder of Spreadsheet. So the process can be run at only Google side. The python script sends only the spreadsheet ID by the request. For this, please do the following flow.
1. Create new project of Google Apps Script.
Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.
If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.
2. Prepare script.
Please copy and paste the following script (Google Apps Script) to the script editor. This script is for the Web Apps.
function doGet(e) {
var spreadsheetId = e.parameter.id;
var file = DriveApp.getFileById(spreadsheetId);
var blob = file.getBlob();
var folder = file.getParents().next();
var pdfFile = folder.createFile(blob.setName(file.getName() + ".pdf"));
return ContentService.createTextOutput(pdfFile.getId());
}
- In this case, the GET method is used.
3. Deploy Web Apps.
- On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
- Select "Me" for "Execute the app as:".
- By this, the script is run as the owner.
- Select "Anyone, even anonymous" for "Who has access to the app:".
- In this case, no access token is required to be request. I think that as the test case, I recommend this setting.
- Of course, you can also use the access token. At that time, please set this to "Anyone".
- Click "Deploy" button as new "Project version".
- Automatically open a dialog box of "Authorization required".
- Click "Review Permissions".
- Select own account.
- Click "Advanced" at "This app isn't verified".
- Click "Go to ### project name ###(unsafe)"
- Click "Allow" button.
- Click "OK".
- Copy the URL of Web Apps. It's like
https://script.google.com/macros/s/###/exec.
- When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful with this.
3. Run the function using Web Apps.
This is a sample python script for requesting Web Apps. Please set your Web Apps URL and spreadsheetId.
import requests
spreadsheet_id = '###' # Please set the Spreadsheet ID.
url = 'https://script.google.com/macros/s/###/exec?id=' + spreadsheet_id
res = requests.get(url)
print(res.text)
References:
