Export a Google Sheets cell range to PDF
- write data to excel
- throw if the annex for the current month is missing
- export excel sheet via fetching URL
- place signature on pdf
- send signed pdf via email
Update: Got it working following this sample and updating it to include the redirect_uri
from the OAuth client IDs file when generating the auth URL.
While I was trying to implement the project's goal, I discovered some things:
As per this SO comment, you can do exactly what I'm looking for (and it works!) by searching this from your browser:
https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/export?format=pdf&portrait=false&gid={SHEET_ID}
From what I've tested though, It's not that simple to get the same rights that the browser gets you while trying to fetch your resource from a Node.js app. A detailed approach for no user intervention is described in this SO post, the conclusion being that is pretty complicated.
Also, a big issue is that the Google Sheets v4 API doesn't offer a way to export a spreadsheet.
The Drive API does, but I don't know if that will work that way, as it seems quite different than the export comment I've linked to above.
Looking at the Google Sheets v4 API source code for a bit, I see that there's no way of modifying the resource paths that are being hit while calling the library's methods.
This SO comment points out a big difference: Your Drive account and the app's Drive account are 2 different things. To access a user's files, including your own, you HAVE TO use OAuth2.0 with an authorization URL.
google-auth-library - npm https://www.npmjs.com/package/google-auth-library
Authentication at Google | Google Cloud https://cloud.google.com/docs/authentication/
Authentication and authorization use cases | Google Cloud https://cloud.google.com/docs/authentication/use-cases#app-users
googleapis - npm https://www.npmjs.com/package/googleapis#oauth2-client
google-api-nodejs-client/quickstart.js at main · googleapis/google-api-nodejs-client https://github.com/googleapis/google-api-nodejs-client/blob/main/samples/sheets/quickstart.js
Using OAuth 2.0 for Server to Server Applications | Authorization | Google Developers https://developers.google.com/identity/protocols/oauth2/service-account#httprest
Node.js client library | Google Cloud https://cloud.google.com/nodejs/docs/reference/google-auth-library/latest
open - npm https://www.npmjs.com/package/open
Download link for Google Spreadsheets CSV export - with Multiple Sheets - Stack Overflow https://stackoverflow.com/questions/33713084/download-link-for-google-spreadsheets-csv-export-with-multiple-sheets
oauth 2.0 - Access Google Drive without OAuth2 - Stack Overflow https://stackoverflow.com/questions/23555040/access-google-drive-without-oauth2#comment113365095_63743304
google api - How do I authorise an app (web or installed) without user intervention? - Stack Overflow https://stackoverflow.com/questions/19766912/how-do-i-authorise-an-app-web-or-installed-without-user-intervention
OAuth 2.0 Playground https://developers.google.com/oauthplayground/
Can't authenticate google service account using Node.js - Stack Overflow https://stackoverflow.com/questions/60645895/cant-authenticate-google-service-account-using-node-js
php - How to export Google Sheets to PDF in a custom format with Google Api v3? - Stack Overflow https://stackoverflow.com/questions/70271257/how-to-export-google-sheets-to-pdf-in-a-custom-format-with-google-api-v3
Files: export | Drive API | Google Developers https://developers.google.com/drive/api/v3/reference/files/export
node.js - Getting 403 from google drive api - Stack Overflow https://stackoverflow.com/questions/56006757/getting-403-from-google-drive-api
How to obtain Google service account access token javascript - Stack Overflow https://stackoverflow.com/questions/28751995/how-to-obtain-google-service-account-access-token-javascript