Connecting to overwritten Google Sheets

shillin2

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!

Bulloko

@shillin2 -

 

 

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?

shillin2

@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.

Bulloko

@shillin2

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.

shillin2

@Bulloko

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)

kshah008

@Bulloko, tagging you in case you haven't seen shillin2's latest response.