How to upload an Excel file to Google Drive as a Google Spreadsheet

How to upload an Excel file to Google Drive as a Google Spreadsheet

In order to upload an Excel file to Google Drive as Google Spreadsheet you need to:

Create a project in Visual Studio. To establish a Connection Manager to Google Drive, you need to use a Google Drive Connection (see Quick Start >> Establish Connection). 



Then, drag and drop a JavaScript Task to the Control Flow canvas:


While configuring the JavaScript Task you need to choose the pre-built Script Rest Web Service Request Task as shown below:


Examples of JavaScript Task configuration - two scenarios:

1. Upload the excel file to Google Drive root folder (My Drive): 
        Example:
      

As shown in the screenshot above, you should put in URL and Body  the following:
URL: https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart&supportsAllDrives=true
Body: {"name":"eventsImport.xlsx","mimeType":"application/vnd.google-apps.spreadsheet"}

After running the package – the file is uploaded as a Google Spreadsheet to My Drive root:

      

2. Upload the excel file to a particular folder in Google Drive (a folder in My Drive):

Example:

      

You should put in URL and Body the following:
Body: {"name":"eventsImport.xlsx","mimeType":"application/vnd.google-apps.spreadsheet","parents":["here you need to fill the relevant Folder ID"]}

For parents, you will need the Google Drive Folder ID (the ID of the Google Drive Folder you would like to upload the excel file to). This Folder ID  is everything that comes after “folders/” in the URL in the Google Drive you would like to use. For example, if the URL was “https://drive.google.com/drive/folders/1dyUEebJaFnWa3Z4n0BFMVAXQ7mfUH11g”, then the Folder ID would be “1dyUEebJaFnWa3Z4n0BFMVAXQ7mfUH11g”.


After running the package - the file is uploaded as a Google Spreadsheet to the desired folder (in the example - Folder: 17534):


    Book a Presales Call


    Are you new to COZYROC and evaluating our solutions? 
    Book a Presales Call with us now and get 5% off SSIS+.

      • Related Articles

      • How to manually generate a Google Services Token?

        A token-based authentication system allows users to enter their username and password in order to obtain a token, which allows them to fetch a specific resource, without using their username and password. Once their token has been obtained, the user ...

      • Where can I find the documentation for the Google Drive Connection?

        You can find documentation related to all the COZYROC SSIS+ Component Suite components from the main products page.  Specific documentation for the Google Drive Connection can be found at the following link.  For many of the components you can find ...

      • How do I access Google Sheets in a Shared Google Drive?

        To access Google Sheets in a Shared Google Drive you have the following two options: Option 1: You should enter for "File Path" the Google Drive file's identifier. You will find the file identifier in the Google Sheets URL. Please look at the ...

      • How to dynamically set the ExcelFilePath when looping through different excel files in a data flow.

        Scenario: I have many Excel files with a same format to convert to text files, and I have created a ForEach Loop to loop through each Excel file to have a data flow with an excel source to flat file.   Question: How can I dynamically set the ...

      • Considerations when choosing REST token storage

        Since version 2.1 in COZYROC SSIS+ access tokens used by REST Connection Manager can be stored not only in the file system, but also in a database (SQL Server).   Using such a centralized storage addresses the following complications with the ...