Getting around the csv limit - attachment to existing googlesheet
Hello!
I had created a auto report based from information sent to me periodically as a CSV via email.
Just to get an idea of how this originally worked:
Gmail ‘new attachment’ recognized via a specific tag
Trigger to activate a macro on the existing google sheet to clear data (report is based on outstanding tasks so continually adding is not productive)
Utilities - Transform to pull the comma based CSV
Google sheets add new line(s) based on specific columns of the csv tranformation
Slack post (pulling formula results from the freshly imported data
Works great! BUT the csv limit of transformation is tiny, we expecting a possible jump to about 7000 lines and the system sending these CSVs are pretty basic so can’t be split out smartly.
What I need to know is can I straight up copy the attachment to a Gdrive folder to then copy that worksheet to an existing worksheet in another workbook? I have tried doing customs targets but it never actually reads the spreadsheet in the testing phase even though googlesheets opens the csv fine.
Any help would be hugely appreciated!
Page 1 / 1
Hi @OoniRick!
It sounds like you’re definitely thinking along the right lines.
Firstly, just to check, are you using the Google Drive Upload File action? If so, make sure that for the ‘Convert to Document?’ field, you select yes - that will convert the csv into a Google Doc.
When you say that the editor doesn’t read the spreadsheet when you test it, could you explain a little more about what you mean? Do you mean that it wont find the worksheets when you use a custom value? Or something else?
Thanks!
Thanks for the reply @Danvers !
Yep document is converted to a sheets type document, shall try rephrase my stumbling block.
Once saved to my drive I then have added a ‘copy worksheet’ but I struggle with selecting the ‘Spreadsheet Containing the Worksheet to Copy’ as if I use the custom google drive option of file exists (which I expected would link back to to the file I just created) it doesn’t find any worksheets.
I obviously can’t select the specific spreadsheet as this will change each time this is triggered.
Thanks for your input!
Hey @OoniRick!
I see what you’re saying. While it’s possible to map the spreadsheet via a custom value, like this…
… the same cannot be done with the worksheet, I’m afraid. There’s nothing from the Google Drive step that provides the worksheet IDs.
BUT, I just saw a note on my end that there’s a Find or Create Worksheet action being added. It should be within the next couple of days, as far as I can tell.
When it is added, I’m hoping you’ll be able to retrieve the worksheet ID, in which case you should then be able to use THAT in your Copy Worksheet step.
I’ll update this thread once that search/action has been added!
That would be perfect Nick! Currently working outside the box and getting a google apps script to pull the data in but seems to be inconsistent and would be much better getting it all in one solution.
Hey @OoniRick!
Great news :)
You should now be able to access this action:
For the spreadsheet, choose the ID from your Google Drive upload step, and for the title you would map the title field form Google Drive then add .csv at the end (that’s what I’ve seen them added as, at least).
Then on the Copy Worksheet step you use outputs from both of the prior steps:
It’s a little clunky, but it should get you what you need!
NOTE: the copy of your worksheet will be named Copy of COriginal Worksheet’s Name]. There’s currently no way around that.
Let me know if this doesn’t work for you, or if you don’t see that action for some reason!
Thanks for this Nick! I shall make a copy and have a play with this. Will in no doubt come in useful later on as well!
