Connecting to overwritten Google Sheets
Hi All!
I am using Domo to create visualizations from a set of several Google Sheets. We have a very large data set on a site that does not have an API, so to get consistently up to date information I have a script that exports a few excel files, converts to Google Sheets and uploads to my drive account. This script runs on a daily basis. Each time the script runs, it overwrites the Sheet that was created the day prior to replace it with the updated information.
I have found that when the data sources tied to those sheets that get overwritten try to update, I get the error shown in the screenshot below. To fix the error, I just go into the details for the data source and hit save (not changing/selecting anything, just re-confirming which sheet I want to connect to).
It seems that Domo still recognizes that the sheet is under the same name, but I'm assuming the fact that it is an overwritten file there is some metadata that confuses the update process. Changing the script to append the new data rather than just uploading a new file and overwriting entirely has more technical complexity than I would prefer to take on for this.
Any help/guidance would be greatly appreciated!
Comments
-
I have a thought on this one. When you go through and overwrite the sheet that's there, does it change the googleSheetKey?
For example, here's a URL to a test doc I made :
https://docs.google.com/spreadsheets/d/1vl_UZDDtrkHX2xp_TG7MEJ7V3pW0AGhJjKITslzPQyQ/edit#gid=0
"name": "googleSheetsKey",
"value": "1vl_UZDDtrkHX2xp_TG7MEJ7V3pW0AGhJjKITslzPQyQ"The googleSheetKey is there in the URL.
I believe that is what the connector is checking for on subsequent runs. Why? Because you can have multiple files with the same name, but these googleSheetKey's will always be unique.
Now I can edit this sheet, change it's size, it, and the key doesn't change. So perhaps I'm wrong, but check yours before and after your run to see if it changed or not.
Also, as a side note. I love what you're doing here!
When your script runs, it takes said data and then turns them into Excel Files. At this point, why not use WorkBench to push them into Domo rather than pushing them into GoogleSheets?
0 -
@Bulloko thanks for the quick response. I checked my URL/sheetkey before and after today's script run and they remained unchanged. Unfortunately, I still got the same error when trying to run the data.
As for workbench, that is something I had considered, but didn't clearly see an OS X-friendly version so I didn't go down that path. Do you know if there is a version I can run on my MBP? I only see a .exe available for download.
0 -
Well.. there goes my theory....
Would you be willing to provide that part of your script that does the excel conversion and replacement into sheets?
If I could replicate this it might help me find an answer.
Ah, yes, WorkBench is a Windows-Only tool. I know of some people running a VM or setting up a dedicated server for it. But it can only run on Windows.
Oh! Another thought, you could use our API and push the data in that way - https://developer.domo.com/docs/domo-apis/data
So instead of converting it from Excel to GoogleSheets and pushing it in. You convert it to a simple CSV and push it into Domo.
0 -
I was hopeful too!
As for the script, I have included the download, convert and upload steps below. Disclaimer: I worked with someone else to have this written, so any detailed questions may be tough for me to answer, but I'll certainly do my best.
def download_file(target_file_url):
# Prepare download request
download_request = urllib2.Request(target_file_url, headers=headers)
# Add cookies
cookies.add_cookie_header(download_request)
try:
# And proceed
response = opener.open(download_request)
content = response.read()
with open('temp.html', 'wb') as r:
r.write(content)
print '[+] Downloaded file'
except:
print '[-] Problem occured during download'
print '[!] Closing...'
sys.exit(-1)
def convert_html_to_xlsx(drive_filename):
try:
# Open both html and xlsx
with open('temp.html') as f:
with xlsxwriter.Workbook(drive_filename) as workbook:
worksheet = workbook.add_worksheet()
tree = etree.HTML(f.read())
# Get all rows
trs = tree.xpath("//tr")
for x in range(len(trs)):
# And all cells
tds = trs[x].xpath(".//td")
for y in range(len(tds)):
# And fill worksheet accordingly
worksheet.write(x, y, tds[y].text)
except:
print '[-] Failed to convert files'
print '[!] Closing...'
sys.exit(-1)
def upload_to_drive(drive_filename,drive):
try:
file_list = drive.ListFile({'q': "'root' in parents and trashed=false"}).GetList()
for result_file in file_list:
if result_file['title'] == drive_filename:
result_file.SetContentFile(drive_filename)
result_file.Upload()
print '[+] File updated successfully'
return True
result_file = drive.CreateFile()
result_file.SetContentFile(drive_filename)
result_file.Upload({'convert':True})
print '[+] File uploaded successfully'
except Exception as e:
print e
print '[-] Failed to upload the file'
print '[!] Closing...'
sys.exit(-1)0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive