Google-cloud-platform API settings for a google-apps-script which converts an excel file to spreedsheet via Drive.Files.insert() function

0
votes

I have a googleform that collects excel file from the users and uploads to drive. As I understand, I should transfer from excel file to spreadsheet to open and get the data. So I use Drive.Files.insert() to convert the file, which requires additional approvals for the script. I enabled the Google Drive API under Resources > Advanced Google Services on google script and linked this google script to a google cloud project. Google Drive API is also enabled on google cloud with "OAuth 2.0 Client IDs ". But my script still cannot pass the google login process and only returns a word file with google login page.

the script:

var Blob01 = UrlFetchApp.fetch(thelink).getBlob();

var fileInfo = {
  title: "test1",
  mimeType: MimeType.GOOGLE_SHEETS,
  "parents": [{
    'id': FolderId
  }],
};

Drive.Files.insert(fileInfo, Blob01, {convert: true});

OAuth settings: Scopes for Google APIs >> ../auth/drive and ../auth/drive.file credentials: OAuth client ID >> Web application google drive api has been enabled and credentials are added as "Google drive api" & "web browser" & "User data"

thanks in advance

1
google-apps-scriptgoogle-cloud-platformgoogle-drive-apigoogle-sheets-apigoogle-oauth
Can I ask you about your question? 1. Where is the excel file? That is in your Google Drive? 2. Can you provide a sample value of thelink? From these information, I would like to think of the reason of your issue and solution. – Tanaike

1 Answers

0
votes

When you use UrlFetchApp to get a file from Drive it will require authorization. That's why you get a word file with google login page.

Try instead to use the DriveApp to get the file from your Google Drive directory. You can for example use the .getFilesByName() function.

Here an example for your use case:

 var blob = DriveApp.getFilesByName("Excel.xlsx").next().getBlob(); // Assuming this file name is unique in your google drive
 var file_ops = { 
    title: "test1",
    mimeType: MimeType.GOOGLE_SHEETS,
    "parents": [
       {
          "id": FolderId
       }
    ]
 };
 Drive.Files.insert(file_ops, blob, {"convert": true});

References:

DriveApp