[Solved] Copy data from Google drive excel file to local file

[mfaisal.ce]

Dear,

I have a excel file on my google drive which is shared by my friend. I want to extract data from that file and store in local excel file at my desktop.

Kindly guide me how to do that

Regrds,

 

[Yousuf Shovon]

Hello Faisal,

I understand that you have an excel file on your google drive and wish to extract data from that file and store it in a local excel file on your desktop. Fortunately, this is all possible with a simple trick. Follow the below steps carefully to do so.

• Firstly, open your file on your drive and click the Share option.

• Afterward, copy the link as shown below.

• Now, open Microsoft Excel and go to the Data tab > Get Data > From Other Sources > From Web.

• Consequently, the From Web dialog box appears and there you need to change the attribute of the link. For that, replace the end part of the link /edit?usp=sharing with /export?format=xlsx.
• Tap OK.

• Subsequently, the Access Web Content dialog box appears. Here, select Connect.

• Lastly, in the Navigator dialog box, click on the Document and tap Load.

• Thus, the required documents pop up in your Excel file.
• If you wish to make any edits to your drive file, update the desktop file using the Refresh button in the Table Design tab.

Best Regards,
Yousuf Khan (ExcelDemy Team)

 

[mfaisal.ce]

Hello Faisal,

I understand that you have an excel file on your google drive and wish to extract data from that file and store it in a local excel file on your desktop. Fortunately, this is all possible with a simple trick. Follow the below steps carefully to do so.

• Firstly, open your file on your drive and click the Share option.

• Afterward, copy the link as shown below.

• Now, open Microsoft Excel and go to the Data tab > Get Data > From Other Sources > From Web.

• Consequently, the From Web dialog box appears and there you need to change the attribute of the link. For that, replace the end part of the link /edit?usp=sharing with /export?format=xlsx.
• Tap OK.

• Subsequently, the Access Web Content dialog box appears. Here, select Connect.

• Lastly, in the Navigator dialog box, click on the Document and tap Load.

• Thus, the required documents pop up in your Excel file.
• If you wish to make any edits to your drive file, update the desktop file using the Refresh button in the Table Design tab.

Best Regards,
Yousuf Khan (ExcelDemy Team)

Dear,

Many thanks to you for the great help but unfortunately, it will not fulfill my purpose.

Actually, i need to extract data dynamically. Excel file on Gdrive has date column and i need to get only that data which is matching with my date on local file.

Hope, i explained my problem.

Regards,

 

[Lutfor Rahman Shimanto]

Dear,

Many thanks to you for the great help but unfortunately, it will not fulfill my purpose.

Actually, i need to extract data dynamically. Excel file on Gdrive has date column and i need to get only that data which is matching with my date on local file.

Hope, i explained my problem.

Regards,

Hi Faisal,

I'm glad that Yousuf's previous solution was of some help to me. However, I understand your requirement now, and you need an answer to extract dynamic data based on a date match, and I have found an idea that will help you reach your goal.

For better illustration purposes, I will share the workbook used to investigate your issues. I will also give you the google sheet link.
Remote Sheet: https://docs.google.com/spreadsheet...xloVcbro_YINrgLDP_Jyk3aBrI/export?format=xlsx

Steps:

• First, open the Local Excel file and follow the procedures Yousuf mentioned.
• When the From Web window appears, copy and paste the Remote Sheet link in the URL section and hit OK.
• Later, choose Remote Sheet and click on Transform Data.

​

• Some empty rows may appear, and to remove the empty rows, click on Remove Rows followed by Remove Blank Rows.

​

• Now we will compare the Local Excel file data with the Remote Sheet data in the Power Query window.
• To attain this, go to the New Source followed by Excel Workbook and open the intended sheet of the Local Excel file.

​

• At this time, click on Combine and Merge Queries.

​

• Subsequently, the Merge window will appear and select the Remote Sheet.
• Choose Joined Date as the Matching column and hit OK.

​

• Now, click on the Expand icon of the Remote Sheet column.
• Later, select the Area and Title columns.

​

• As a result, the intended table will display like the below one.
• Then, press Close and Load, followed by Close and Load To.
• At this stage, check New Worksheet and hit OK.

​

• Thus, we extracted data dynamically and solved the issue.

Best regards,
Lutfor Rahman Shimanto