Copying text from an email and pasting it into a specific row and column in Google Sheets

[MikeMc]

Hi guys, 

I've recently downloaded Alfred and really like it. I'm trying to create a workflow now and have been struggling with how to go about it. I've tried to lay my problem out below

1) I receive an email from an address that I have saved in a Google sheet.
2) I open up the email and see that they have pasted a link/some text that I need in the email.

3) I want to use a trigger that copies the text I select and adds it to a specific row and column in my Google sheet. 

I'm using Zapier and feel comfortable with setting up Zaps with Alfred. 

However, my issue comes when matching the email I'm on with the correct row and column in the spreadsheet. In my spreadsheet, I have the recipients email address and even the message thread URL. So I could use these as a unique modifier quite easily. 

I'm just not sure how to set it up so that I have these bits of data to use in my zap.

Any guidance on this would be very much appreciated. Thanks everyone.

- Mike 

[MikeMc]

Is anyone able to help or give a little bit of insight into how to solve this problem?

Thanks so much everyone

[deanishe]

Have you tried the Zapier forum?

 

It seems that you're using Zapier's service and Zapier's workflow, so Zapier's forum would be the logical place to ask.

[MikeMc]

After re-reading this I don't think I explained myself properly. 

The Alfred part I would like is to "copy the current web page URL along with some text that I select".

After searching around I found this - 

Which allows me to copy the current webpages URL, but I also then want to add some text that I select. The next stage is where Zapier would come in I guess. I would use the info gathered by Alfred to trigger my Zap. 

But if this is a question better asked in the Zapier forum then no worries I'll ask there instead. Thanks for your help Deanishe.
 

[deanishe]

1 hour ago, MikeMc said:

The Alfred part I would like is to "copy the current web page URL along with some text that I select".

 

Strictly speaking, that’s also not really an Alfred question, as you’re asking about browser automation  

 

However, there are lots of forum users who can answer it. But not if you don’t tell us which browser you’re using…

Edited October 20, 2017 by deanishe

[MikeMc]

Lol. Sorry, can you tell I'm a complete newbie at all this! The browser I'm using is Google Chrome. 
Thanks again for your help so far. I really have no idea where to start with all this so came to you guys first. I know I'll get it eventually though  

If there is somewhere else I'd be better off asking all this stuff then point me in the right direction.

[deanishe]

You can get the URL of the frontmost tab with the following JavaScript, as described in the stickied thread:

Application('Google Chrome').windows[0].activeTab.url()

You can get the selected text with this:

Application('Google Chrome').windows[0].activeTab.execute({javascript:'window.getSelection().toString()'})

Edited October 20, 2017 by deanishe

[MikeMc]

Thank you

I shall give this a go and see if I can work it out. 

[deanishe]

If you can be more specific about what you're trying to do, I can provide more useful help.

[MikeMc]

Thanks buddy. I tried to upload screenshots into here but the file sizes were too big. So I put together a Google Doc to help explain what it is I'd like to do

https://docs.google.com/document/d/19mSWhjjrKCZpSXssQV9wv3-VvhKakmnDKqN3JAjBqq4/edit?usp=sharing

Thanks so much. Even just pointing me in the right direction before helped a lot  
 

Edited October 20, 2017 by MikeMc

[deanishe]

Here's a workflow that gets the data from Chrome and sets corresponding workflow variables (url, threadID and selection).

It's not at all clear what you want to do with the data next, so I've just connected a Large Type output that shows them in a big, black window.

[MikeMc]

Hey Deanishe, 

This is so cool! It's way better than I thought it could be and definitely on the right track. 

I've updated the Google Doc with that I wanted to do with the data. Does it make things any clearer?

https://docs.google.com/document/d/19mSWhjjrKCZpSXssQV9wv3-VvhKakmnDKqN3JAjBqq4/edit

If not do I need to set up a github account so that I can share the workflow with you in here?

* Also, I'd really like to understand and build automation stuff for myself. Is the most relevant language to learn for this python? It seems to be the recommended often but it would be great to get an experienced opinion on this. 

 

 

Edited October 21, 2017 by MikeMc

[deanishe]

6 hours ago, MikeMc said:

Does it make things any clearer?

 

A bit, but the screenshots are JPEGed to death and I can’t read anything. Please export and upload your workflow somewhere (Dropbox?), so I can download it and see it for myself.

 

6 hours ago, MikeMc said:

Is the most relevant language to learn for this python? It seems to be the recommended often but it would be great to get an experienced opinion on this. 

 

Python would be a good choice. Ruby is as capable, but not as easy to understand. PHP is an awful language, but useful.

[MikeMc]

Sure! Sorry about my dodgy Google Docs efforts.

https://www.dropbox.com/s/7n34p9errdbcmf3/My Gmail Copy Attempt.alfredworkflow?dl=0

Can you get my workflow through this link? 

[MikeMc]

26 minutes ago, deanishe said:

Python would be a good choice. Ruby is as capable, but not as easy to understand. PHP is an awful language, but useful.

 

Thanks

[deanishe]

I've no idea how Zapier works, but could you explain what this is supposed to mean:

Quote

 [The actual URL] + [Text I select]

 

Like, why would you want [http://mail.google.com/...] + [Dear Mike,...] added to a spreadsheet?

 

There are two chief problems with your workflow:

1. You're trying to add {query} to the URL, but {query} isn't set. The data are in the variables, so you'd use $url and/or $selection instead.
2. You can't just add arbitrary data to a URL and expect it to work. It needs to be URL-encoded first.
   

 

I'll fix the workflow if you can explain exactly which data you want adding to the URL.

[MikeMc]

This workflow is for SEO, and it would help me with the emails I receive for "guest-posts." People often write the article title they prefer in the emails they send to me. So the [Text I select] is most likely to be something along the lines of [The X best things to do with Alfred.]
 
Rather than copying this title, finding the correct row in my spreadsheet and pasting it in manually. I wanted to do something cool with Alfred to help me. I guess it wouldn't save me that much time but feels like a nice way to get into all of this.   
 

In Zapier, I need to use a "unique identifier" to find the correct row, and I already have the message thread URL in my spreadsheet. So having the message thread URL in my Alfred output would act as Vlookup does. Then, once Zapier has found the correct row it would paste the article title into the correct column for me.
 

(I am certain that all of this can be done without Zapier, but this is the idea I have for now as my knowledge is so limited). 
 

The data I'd like adding to the URL would be [Dear Mike,...] like above. (Which is how it was in the original workflow). It's just with the way I set it up, Zapier returned only the actual letters "URL" and you explained why above. 
 

I'd be happy to make a quick video at the end of all this to fully explain everything working :)

Thanks Deanishe
 

* On a side note - I'd really like to learn python through practical application. I found this course - https://www.udemy.com/automate/?couponCode=50_PERCENT_OFF#curriculum. I'm guessing you haven't done the course itself, but do you have any similar resources, recommendations for places to start?

Edited October 22, 2017 by MikeMc

[deanishe]

3 hours ago, MikeMc said:

The data I'd like adding to the URL would be [Dear Mike,...] like above.

 

Sorry. What I meant specifically is "what data format does Zapier expect?" Is [ABC]+[XYZ] the way to tell Zapier to put ABC in one column, and XYZ in the next?

 

It doesn't matter. I've looked at the documents myself. You need to POST your data to the webhook endpoint, either as JSON or x-www-form-urlencoded.

 

What are the field/row names:

 

 

Assuming your fields are called URL and Text, your curl command should look like this:

curl -ssL -X "POST" "$ZAP_URL" \
     -H "Content-Type: application/x-www-form-urlencoded; charset=utf-8" \
     --data-urlencode "URL=$url" \
     --data-urlencode "Text=$selection"

 

3 hours ago, MikeMc said:

On a side note - I'd really like to learn python through practical application

 

I don't think it's necessary to pay for a Python course. There are loads of free resources.

 

https://www.reddit.com/r/learnpython/wiki/index#wiki_new_to_programming.3F

 

Edited October 22, 2017 by deanishe

[MikeMc]

10 minutes ago, deanishe said:

"what data format does Zapier expect?" Is [ABC]+[XYZ] the way to tell Zapier to put ABC in one column, and XYZ in the next?

Yeah, it doesn't matter. I was going to do something in Zapier where I format the text via it's "Transform" feature. Below is a screenshot of the kind of stuff that the Transform feature does.

  

The data from Alfred could actually just be one long line of text, (which might easier)? I would have to see how the data is displayed from Alfred, but it's likely that I could split the text via a character or via a space.

In the example we have above - "[ABC] + [XYZ]" the repeatable character I could use to split the text is the "+"

Then I could organise [ABC] and [XYZ] as separately, which is what I need to do.
 

35 minutes ago, deanishe said:

https://www.reddit.com/r/learnpython/wiki/index#wiki_new_to_programming.3F

Thanks for the list. I'm looking forward to reading up on all of this

[MikeMc]

Wait!!!
It works!!! 

Oh sweet as hell. I'm out at the moment, but I'll make a video and run through it later. 

Thanks so much for your help man 

 

[deanishe]

7 minutes ago, MikeMc said:

I'll make a video and run through it later

 

That's not necessary.

 

32 minutes ago, MikeMc said:

Yeah, it doesn't matter. I was going to do something in Zapier where I format the text via it's "Transform" feature

 

That would be a silly way to do it when multiple variables are already supported.

 

Edited October 22, 2017 by deanishe

[MikeMc]

Lol ok. No worries, thanks for your patience with me. 

See you around.

[deanishe]

NP. Thanks for the beer money!

[MikeMc]

1 hour ago, deanishe said:

NP. Thanks for the beer money!

Anytime. 
 

1 hour ago, deanishe said:

That would be a silly way to do it when multiple variables are already supported.

I saw how you set it up. That is a much better way to do it. You seem to be quite good at this stuff