CODEWITHSUNDEEP
javascriptnode.jsexcelgoogle-apigoogle-drive-api
Lucas Modolo
Lucas Modolo

Reputation: 177

Upload a excel file with google drive api

I'm having a problem with the google drive API.

I'm trying to upload an excel file with this API, but it's not working. Even copying the google API documentation doesn't work.

Here is a sample of my code:

@Get('teste')
async teste(){
    const keys = require(path.resolve('src', 'files', 'api', 'keys'))
    const client = new google.auth.JWT(
        keys.client_email,
        null,
        keys.private_key,
        ['https://www.googleapis.com/auth/drive.metadata.readonly']
    )

    client.authorize((err, tokens) =>{
        if(err){
            console.log(err)
            return;
        } else{
            this.gdrun(client)
        }
    })

}

gdrun(client){
    const drive = google.drive({version: 'v3', auth: client});

    var fileMetadata = {
        name: 'My Report',
        mimeType: 'application/vnd.google-apps.spreadsheet'
    };
    var media = {
        mimeType: 'application/vnd.ms-excel',
        body: require(path.resolve('src', 'files', 'excel', 'solargroup.xlsx'))
    };
    drive.files.create({
        resource: fileMetadata,
        media: media,
        fields: 'id'
    }, function (err, file: any) {
        if (err) {
            // Handle error
            console.error(err);
    } else {
        console.log('File Id:', file.id);
    }
    });
}

I received this error: enter image description here

Upvotes: 3

Views: 2652

Answers (1)

Tanaike
Tanaike

Reputation: 201428

I believe your goal as follows.

  • You want to upload a file (XLSX file) to Google Drive of the service account.
  • You want to achieve this using the service account with googleapis for Node.js.
  • From your script, I thought that you might wanted to upload a XLSX file as Google Spreadsheet by converting.

Modification points:

  • When you want to upload a file to Google Drive, in this case, please use the scope of https://www.googleapis.com/auth/drive instead of https://www.googleapis.com/auth/drive.metadata.readonly.
  • When you want to upload XLSX file as the XLSX file, the mimeType is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.
  • When you want to upload a file, body: require(path.resolve('src', 'files', 'excel', 'solargroup.xlsx')) cannot be used. In this case, please use body: fs.createReadStream(path.resolve('src', 'files', 'excel', 'solargroup.xlsx')). I thought that your error message might be due to this.
  • When you want to retrieve the file ID of the uploaded file, please modify file.id to file.data.id.

When above points are reflected to your script, it becomes as follows.

Modified script:

From:

const client = new google.auth.JWT(
    keys.client_email,
    null,
    keys.private_key,
    ['https://www.googleapis.com/auth/drive.metadata.readonly']
)

To:

const client = new google.auth.JWT(
    keys.client_email,
    null,
    keys.private_key,
    ['https://www.googleapis.com/auth/drive']  // Modified
)

And also, please modify your gdrun() as follows.

gdrun(client){
  const drive = google.drive({ version: "v3", auth: client });

  var fileMetadata = {
    name: "My Report",
    mimeType: "application/vnd.google-apps.spreadsheet",
  };
  var media = {
    mimeType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",  // Modified
    body: fs.createReadStream(path.resolve('src', 'files', 'excel', 'solargroup.xlsx')),  // Modified
  };
  drive.files.create(
    {
      resource: fileMetadata,
      media: media,
      fields: "id",
    },
    function (err, file) {
      if (err) {
        console.error(err);
      } else {
        console.log("File Id:", file.data.id);  // Modified
      }
    }
  );
}
  • In this case, please use const fs = require("fs").

Result:

When above script is run, the following result is obtained.

File Id: ###fileId###

Note:

  • Your script uploads a XLSX file to Google Drive of service account as Google Spreadsheet. In this case, you cannot directly seen the uploaded file at your Google Drive. Because the Google Drive of the service account is different from your Google Drive. When you want to see the uploaded file at your Google Drive, please create a folder on your Google Drive and share the created folder with the email of the service account. And then, please upload the file to the shared folder. By this, you can see the uploaded file on your Google Drive with your browser. For this, please modify fileMetadata as follows.

      var fileMetadata = {
    name: "My Report",
    mimeType: "application/vnd.google-apps.spreadsheet",
    parents: ["### folderId ###"],  // Please set the folder ID of the folder shared with the service account.
  };

  • In above script, the maximum file size is 5 MB. Please be careful this. When you want to upload a file more than 5 MB, please use resumable upload. Ref

References:

Upvotes: 5

Related Questions