Upload And Convert Google Drive Spreadhseet

Describe the issue/error/question

I want to update the metadata for a file on Google Drive and I can’t seem to find a way to do so using any of the built-in nodes.

For context, what I am exactly trying to do is upload an excel file to Google Drive and convert it to a Google Sheets file. The conversion can be done using the update API method along with the mimeType metadata parameter, but it is also a requirement that the file content and its metadata be sent in the same HTTP request using multipart form data (see docs: multipart upload method).

Note that there is an existing feature request to add this as built-in functionality to the Google Drive node, but I am looking for a solution that I can use today without having to wait for it to be implmented/released.

What I have tried so far:

  • Using the built-in Google Drive node: this does not support uploading a new file with the update API request (existing feature requests: https://community.n8n.io/t/google-drive-file-update/10797, https://community.n8n.io/t/converting-document-when-uploading-to-google-drive/7573). Sorry for the weird links, I’m limited to 5 links per post as a new user.
  • Using the HTTP request node
    • This does not support setting content type header for each multipart form data section, which is required for this to work
    • I think the closest I got to getting this to work was manually constructing a the multipart body as RAW (I am using a function node to get the binary data to add to the body). However, when I tried this I got a rejected HTTP response back from the API and I’m not sure how to view the full HTTP response or the HTTP request that was actually sent to debug the problem
  • Using a function node: I could not get an import of axios or request-promise-native to work in order to make the HTTP request. I am running in Docker and am using NODE_FUNCTION_ALLOW_BUILTIN=*, but trying to import either module like const request = require('request-promise-native'); I still get an error VMError: Cannot find module 'request-promise-native'.

I think I could use advice in the following areas:

  • How can I view the raw HTTP request/response from the HTTP request node?
  • Is there something I am missing to get node module imports working in the function node? Also, what is a ‘built-in’ node module supposed to be? The documentation does not clarify what that means, but I have been assuming it’s the packages included in the core n8n package?
  • Any ideas for a different approach I could take to solve this problem?

What is the error message (if any)?

Included above

Please share the workflow

Share the output returned by the last node

Output from the the HTTP request node when constructing the multipart form data manually:

Information on your n8n setup

  • n8n version: 0.170.0
  • Database you’re using (default: SQLite): SQLite
  • Running n8n with the execution process [own(default), main]: own
  • Running n8n via [Docker, npm, n8n.cloud, desktop app]: Docker

Okay, I just needed to stick with this a bit longer to get it figured out. The key is to use a two-part resumable upload rather than messing around trying to get the multipart form data formatted correctly for upload in a single request. Doing the upload in two parts made it fairly simple to use HTTP node to get this done.

For future readers, my workflow looks something like this

  1. Get the file to update. In my case this is from the IMAP email node which triggers the next HTTP request node, but it could be any node that provides some binary data to upload
  2. Send the initial request with the file metadata to the Google Drive API with an HTTP Request node
    • Details on the format of the request can be found here in the API documentation for a resumable upload and also in either the create or update endpoint documentation, depending on the type of operation you are doing
    • You will want to make sure the request body is JSON content containing the file metadata. The important part for this metadata is to set the mimeType to application/vnd.google-apps.spreadsheet, which indicates the uploaded file should be converted to a Google Sheet
    • Set a X-Upload-Content-Type header with the original mime type of the file you are uploading. Make sure this mime type matches one of the types supported for conversion in the about API endpoint
    • It is also necessary to set the Full Response option to true to get the response headers
  3. Use a merge node to merge the result of the initial HTTP request with the first node that contains the binary data of the file
  4. Use a second HTTP Request node to upload the file content
    • Details on the format for this request can be found in the API documentation for a resumable upload
    • The URL for the request should be retrieved using an expression for the Location response header from the first HTTP request
    • The request body should contain the binary data of the file

Hopefully this helps someone in the future, took me a while to figure out!

2 Likes