Import Google Sheet

Hi Team,

I am not able to import this Google Sheet:
https://docs.google.com/spreadsheets/d/14quQPFErG-hlpsrNgYcX85vW7JMMK5X2vNZrafRcH8c/htmlview?pru=AAABcd6_5n0*AmBOg0Pvr44nCM1277gfTg

I am not able to import this sheet using Import from Web API step.

I used the step download from URL into csv/txt but still not able to upload it back using any of the default Coding available in “Import csv” (or as txt) option.

Please help. It’s just a Google Spreadsheet!

Regards,
Sumit

Hi Sumit,

To import from a Google Sheets document use the “Import Google Sheets” action.

To export data into a Google Sheets document use the “Export Google Sheets” action.

Hi Dmitri,

Google sheet import/export only showing “My drive” which excludes all shared sheets or directories (shared by others - but with editing rights)

Is this correct? If so, is there al alternative you can recommend to achieve a shared cloud sheet like google with editing, thanks

Hi Aaron,

Yes, that is correct.

In order to access those shared sheets, you have to add them to your “My Drive” catalog with the "Add to “My Drive” menu item: https://support.google.com/drive/answer/2375057

I realize this is an old thread, but this info is not in the docs online (connectors:googledrive [EasyMorph Help]), and it would be very helpful. Because many teams will likely work with Google Sheets owned by other departments or teams, I look forward to seeing more detail on this in the documentation.

The reason I’m posting is I’m getting an error that is wrong. I’ve connected to a shared Sheet by Adding it to My Drive, I can open the Sheet no problem – all the Sheet names/columns are an exact match to my test Sheet that I own – and it’s throwing the error:

Error: File ‘Log’ found but it’s not a spreadsheet.
Source: action “Import Google Sheets”, module “Main”, table “CURRENT (not Completed)”

I know for a fact that this file is a valid Sheet because it opens just fine in my browser.
I didn’t think there would be any difference in the error as it’s displayed in EasyMorph vs. what’s actually copied to the clipboard.
The source info is not from this error as it’s shown in EasyMorph; now that I’ve pasted it here in this moment I have some idea what may really be wrong: the original name of the Sheet was that under ‘table’, but it’s been renamed. I’m curious if this is an internal issue within Google Sheets or EasyMorph itself…

My one hunch: renaming the Sheet back to the original name tells me “Spreadsheet file […] not found.” I’ve waited over 15 minutes since renaming it within Google Sheets so I know the connector can properly see the change (same result a day later), but it seems to have moved me further from the solution…
(To be clear, hitting the Refresh button under Connector Manager seems to remove the error, immediately recalculate the entire connection, and reproduces the current problem error.)

andrew.rybka, dgudkov: any thoughts?

Hi Doug, and welcome to the Community!

Please make sure that the target file in the My Drive folder is an actual file and not a shortcut?

It seems that currently Google Drive have two options to add a shared file to My Drive:
Add shortcut to Drive and Make a copy.

The first option will create a shortcut that EasyMorph doesn’t support.
And the second option will create an actual copy of a file that EasyMorph can import.

You are very generous Andrew, it sounds like I’m not the first you’ve helped who’ve been stung by this issue. You’re saying I need to “Make a copy”

I’m afraid the “Add to My Drive” article you referenced a year ago currently outlines creating a shortcut, not “Make a copy”. It directly instructs implementing shortcuts and I’m afraid that’s contrary to your advice today, unless I’m missing something you felt was clear? It seems like the article you originally linked to may have since changed?

I’m sorry, you’re not going to want to hear this, but “Make a copy” is inadequate. The Sheet I’m working with will never be owned by me; for production & efficiency it must be a shared Sheet – “Make a copy” causes further problems, fractures data, causes headaches with record updates between file versions, may require version tracking/history and causes unnecessary cleanup/validation. And that seems to have left Profile - Aaron_Hayden - EasyMorph Community - Data preparation professionals and enthusiasts, myself and others back at square one:
how am I supposed to work with a shared Sheet?

In the meantime I was seriously considering that I may have to jump through manually authorizing transfer of ownership of the Google Sheet temporarily during off-hours to handle the data transformation and then returning ownership.

I’m afraid Google is committed to shortcuts and this problem is here to stay. According to Google’s own blog (Google Workspace Updates: Create pointers to any file or folder in Google Drive with shortcuts), “After September 30, 2020, all files already living in multiple locations in My Drive will gradually be migrated to shortcuts.”

One user asked about Google’s “add to my drive” changed to “add shortcut to my drive” back in March 2020 (it generated 145 concerned replies before Google locked it after just 9 months of complaints, and was Upvoted 682 times before I found it today) with:

Previously I was able to “add to my drive” folders from the “shared 
with me folder”. That allowed me to then use Google drive File stream
and access them as if it was in a another hard drive in my computer.

which garnered a verified suggestion to a hidden workaround using Shift+Z but I have yet to have luck with that:
https://support.google.com/drive/thread/36734924/“add-to-my-drive”-changed-to-add-shortcut-to-my-drive?hl=en
In case the above link ever dies, the solution was outlined by Rotary Steve (Rotary Steve - Community):

Yes, they changed it.  While in Drive, click on the "Settings" option in 
the upper right, "Keyboard shortcuts".  There is a long list there of the
possible keyboard shortcuts. 

The shortcut you will need to use now is click on the file you want and
then "SHIFT + Z" which already had worked, but they changed the menu.

I look forward to EM catching up with Google’s seemingly difficult-to-find changes to Google Drive & the API (since their blog post is a year & a half old and did not come up directly in web searches for me), where they ultimately intend to eliminate ALL duplicate files, and replace them with shortcuts to the original file.

I’m afraid this solution no longer works… In Google Drive, under “Shared with me” is the desired Sheet; click it to select it, hitting Shift+Z brings up the “Add Shortcut” popup.

Thank you for bringing the subject up. Google has changed a few things about cloning and shortcuts, so we need to see how we can respond to that.

Meanwhile, if you are in contact with the document owner, try asking them to provide (e.g. using EasyMorph) the data in some other way – e.g. in a different format (CSV?) or via a different channel (email?).

Andrew or I will get back to this topic once we have any updates.

I appreciate you dgudkov
I’m afraid getting the data isn’t as problematic as adding transformations into likely already-changed records.
Temporary Solution:
Temporarily changing ownership of the Sheet was considered, but we’ve decided the easiest method is to create a new Connector with the owner’s Gmail account to avoid adding other headaches at the moment. With any luck it’s very little code that needs updating in order to account for Google’s change in procedure. I’m definitely interested in helping test whatever your team comes up with! I look forward to working with you and andrew.rybka in the future.

Hello Doug,

We’ve added support for shortcuts for Google Sheets files to the latest EasyMorph version. You can get it on our download page.

You’ll have to use the “Add shortcut to Drive” item from the right-click menu to add a shortcut for a shared file to your “My Drive” folder. Then you’ll be able to use that shortcut with the “Import Google Sheets” and “Export Google Sheets” actions.

1 Like

Thank you for making such a useful tool, you all clearly work your butts off. You guys are very competent and I look forward to continuing to work with each other.

Warm Regards,

-Doug Hodgson