-4

I have an Excel sheet with a worksheet named test_sheet. I have a Google sheet named G_sheet having 5 tabs. I would like to automate the process of copying the contents of Excel worksheet test_sheet and paste it in a specific tab, say 'sheet1', of the Google sheet. How can be done this using excel VBA?

I am using Excel 2013.

Community
  • 1
  • 1
user1315789
  • 3,069
  • 6
  • 18
  • 41
  • Sure you wanna do that? Why don't you feed google sheet from the beginning? For doing what you want you'll need your excel file in a specific place, python code to read that and transform into a csv-like file (xlwings might do that), then more python code and 'setting up' to connect to google sheets API and write there. And a cronjob (or something like that) to do this periodically. – Alex Nov 06 '17 at 16:17
  • 3
    there is a Google Spreadsheets python library, all you need is some glue code, what have you tried so far? – georgexsh Nov 06 '17 at 22:47
  • Similar question here: https://stackoverflow.com/questions/9690138 – Steve Chambers Nov 07 '17 at 10:15
  • here's a link even, to the spreadsheets API. https://developers.google.com/sheets/api/guides/values – smundlay Nov 13 '17 at 03:16

4 Answers4

2

Your going to need

  1. Pandas
  2. Watchdog
  3. Pygsheets

Pandas read xlsx/csv files, convert them to dictionaries, so that you can apply a batch update using pygsheets.

Watchdog, to watch you xlsx files for modifications. When a modification is made, you re-read the files with pandas, and update the spreadsheet on google using pygsheets.

enter image description here

Mo. Atairu
  • 753
  • 8
  • 15
2

I may be misinterpreting the question, but if you just want to copy a file from Excel to Google Sheets you can just upload it with their web interface and it will be automatically converted.

If you need the file to be synchronized, you can use the Google Drive sync client, or save the Excel sheet to OneDrive and use Zapier to synchronize the file.

If, for whatever reason, you need to upload the file programmatically, it will be easier to just automate the user inputs with a program like AutoHotkey than to create a Python program to do this.

If you really need the program in Python (which is after all one of the most useful languages for data processing), you can use xlwings to read the sheet and the Google Sheets API for output.

Another thing you could do to speed up the file read-in would be to add some simple VBA code to the Excel file to output the entire contents to a format more easily readable by python, or exporting the whole sheet as csv and then using the python csv library to read it in.

Another question is why you need it in Google Sheets format anyway. If you just need the file to be accessible via a link, you could upload the sheet to OneDrive and make it accessible. If you need it to be accessible by other programs over the internet, you could transfer it directly or make the file accessible over FTP.

speedstyle
  • 142
  • 4
  • 11
2

If you have Google's OAuth2 credentials,:

http://gspread.readthedocs.io/en/latest/oauth2.html

just use:

https://github.com/burnash/gspread
jimh
  • 1,651
  • 2
  • 15
  • 28
1

You could use googles program called Backup and Sync to easily save any folder on your computer to your google drive.

You just have to set up the sync settings to where it only saves that one excel sheet to a place on your computer. And then every time you save the doc it will automatically be synced with the copy of the excel sheet on your google drive.

Logan Eldridge
  • 45
  • 10