How to download a spreadsheet from google drive using a service account and typescript/node.js

This question already has an answer here:

How to download dynamic files from google drive

(1 answer)

Closed

3 months ago

.

I’m trying to download all the spreadsheets contained in a folder using a service account.
I cannot find a solution, I hope someone could help me.

I authenticate and I get successfully drive.files.list but then I can’t download files.
This is my code

import { google } from "googleapis";
import { privatekey } from "./privatekey";
import { createWriteStream, writeFileSync } from "fs";

let jwtClient = new google.auth.JWT(privatekey.client_email, undefined, privatekey.private_key, [
    "https://www.googleapis.com/auth/drive",
]);
//authenticate request
jwtClient.authorize(function (err, tokens) {
    if (err) {
        console.log(err);
        return;
    } else {
        console.log("Successfully connected");
    }
});

const folder_id = FOLDER_ID
let drive = google.drive("v3");
drive.files.list(
    {
        auth: jwtClient,
        q: `'${folder_id}' in parents and trashed=false`,
    },
    function (err, response) {
        if (err) {
            console.log("The API returned an error: " + err);
            return;
        }
        var files = response?.data.files;
        if (files?.length == 0) return;
        files?.forEach(async (file) => {
            let fileId = file.id;
            fileId == null ? (fileId = undefined) : (fileId = fileId);
            //writeFileSync(`./cartella/${file.id}.xlsx`, "");
            prova(jwtClient, fileId, file.mimeType);
            //await getFileFromStream(jwtClient, fileId, file.mimeType);
        });
    }
);

function getFileFromStream(auth: any, fileId: any, mimeType: any) {
    const destPath = `./cartella/${fileId}.xls`;
    const dest = createWriteStream(destPath);
    return new Promise(async (resolve, reject) => {
        const drive = google.drive({ version: "v3", auth });
        drive.files.get({
            fileId: fileId,
            alt: "media",
        }),
            (err: any, res: any): void => {
                res.data
                    .on("end", () => {
                        console.log("Done");
                    })
                    .on("error", (error: any) => {
                        console.log("Error during download", error);
                    })
                    .pipe(dest);
            };
    });
}
function prova(auth: any, fileId: any, mimeType: any) {
    const destPath = `./cartella/${fileId}.xls`;
    const dest = createWriteStream(destPath);

    const drive = google.drive({ version: "v3", auth });
    drive.files.export({ fileId: fileId, mimeType: mimeType },{responseType: "stream"}, (err: any, res: any) => {
  if (err) {
    //   handle error
          console.log("error: ",err)
  } else {
          if (res == null) return
    res.data
      .on("end", function () {
        console.log("Done");
      })
      .on("error", function (err: any) {
        console.log("Error during download", err);
      })
      .pipe(dest);
  }})
}

First of all I added the service account to the editors of the folder in google drive

The function getFileFromStream returns a big error, but I think that the most interesting thing is this one

domain: 'global',
reason: 'fileNotDownloadable',       
message: 'Only files with binary content can be downloaded. Use Export with Docs Editors files.',            locationType: 'parameter',    
   location: 'alt'     }   ]

So I tried to use drive.files.export, but the response is

status: 400,  
   statusText: 'Bad Request',   
   request: {       
       responseURL: 'https://www.googleapis.com/drive/v3/files/file_id/export?mimeType=application%2Fvnd.google-apps.spreadsheet'   
  }

I also tried a different authentication method like the one proposed here:
Setting up Google Drive API on NodeJS using a service account
but it still does’t work

What am I doing wrong?