GitLab Learning & Development, 3 Sep 2020

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: How To Set up Google Sheet for Zapier Certificates

Description

This video walks through how to set up a spreadsheet when you have multiple knowledge assessments that you are using to trigger a certificate.

More information can be found on our certification page: https://about.gitlab.com/handbook/people-group/learning-and-development/certifications/#step-2-google-sheets

A

So we will walk through how to get the sheet set up to get a certification sent or a certificate sent with zapier. So I opened up these other sheets that you have for each of the quizzes and um it looks like you have them set up with, like the passing results, tab, which is perfect. That's the first step so.

A

Yes, this is the formula um perfect okay and then um I was trying to remember what order are these in? Is it epics and then merge requests and then new handbook page? Yes, that's the okay! That's the order! Okay, perfect! So what I did was I went here and I clicked new oops um sheet and I made this sheet, which is blank right now.

A

um So what I will do is, let's see I'll name, this first sheet pass epics and then I use what I use is this same formula, so I just go here and copy it and pull it over here into cell a1.

A

um You'll have to allow access for the sheet.

A

Okay, so it looks like it brought it over and then I will create another one. Pat pass.

A

Merge requests do the same.

B

Thing here, oops goodness.

B

Back to the certification, one again paste in a1 and yeah, you have to allow access for all all of them to connect and then pass.

B

New handle page.

B

A

Okay cool, so all of um the passing information should be pulling over here. So what we will do next is we'll have a pass, all three tab and then okay, so we'll just start with this pass all three so um and it would be past all. However,.

B

A

Number, if you have four for or like the remote certification, is pass, um they have to pass ten. um So what you do next is: okay, so you'll, just like type in to these to a1. um So we'll do.

B

Epics first name.

A

And this is like what they would enter. Oh, this is first and last. Okay, first.

B

Just by name and then um epic's email address.

B

B

B

This is pass paul three and then.

B

A

Okay, so all this information is what we need to.

A

To populate, what's on the certificate, so we need their name, their email address um to know where to send it to. We need to know that they passed all three and then we need to have a the date that they passed and since the handbook page is the last one that they should take and pass that's. Why I'm going to pull that date, because maybe they took the email address one five days ago they took or they took the epic one five days ago.

A

They took the mr1 two days ago and then today they took the handbook page. One it'll have the date that they took the handbook page one on the certificate. So what we will do now is, um let's see so we'll do equal go into the epics tab.

A

And do that so then it pulls over and then you'll need to do copy it. This formula down through all the cells, so I just do command command copy and then hold down command and hit the down arrow. Oh, no! That's wrong!.

B

A

So command copy and then hold down command shift and the down arrow and it'll highlight all the cells in that row and then keep holding down command and hit v and it'll paste the formula you can see that it pasted here so um and then we need to do that same thing with all these so equals we'll go to the ethics tab.

A

Email address enter so you can see it pulled over your email address and then command copy shift.

B

Down paste it and then this equals the merge request so request.

B

A

B

B

A

I always like um click onto one of the cells that I think I just copied in just to make sure that the formula is in there and that it actually did copy, because I have missed it before like. I did the time before and then.

B

B

B

A

One I just missed.

B

Again, oh and I have.

B

I did it wrong anyway, so equals page.

B

Command copy.

B

Okay, so I copied down and.

A

Then for pass all three.

A

I use I normally just open like a sheet I've used before, so I have the gitlab 101 sheet over open here on the side. I use this formula, so um it looks for it uses the email address in um cell b2.

A

So if it was on this one it'd be cell b8, um so it uses the email address, that's in b2 and it looks for it in cell or column c and d and then, um if it does it'll put true if it it finds the email address in all the columns, it'll put true in column e. If it doesn't it'll say false, so you can see this one's pretty easy because they're all all right across from each other. That's not always the case, but um so it will be like that.

A

So again, just command copy command shift down and paste, and you can see it's in all those and then this one.

A

And then this one, I will do the same formula that I use for these ones, so I'll just do equals and then go to the new handbook page timestamp there and one thing I do with the time stamp is: I will just select the column and then do data.

A

No, it's format, format, number more formats, more date and time formats, and then I change it to the iso date formatting.

A

um So then it is a little bit cleaner on the certificate like it doesn't have the exact time they completed it. It just has the date.

A

um So we have this one set up, but this doesn't trigger the certificate because with zapier to trigger a certificate, you need to have a new row added to a spreadsheet and when a new row is added to this spreadsheet, it doesn't necessarily mean someone has completed it so like if I took um epics the epics knowledge assessment, my name would be added here and my email address, but I haven't done these two yet so over here it would say false, so it wouldn't trigger a certificate.

A

So we need to use this other sheet to do.

A

What will trigger the certificate so we'll do pass 201, cert and then um okay again, I'm probably not gonna. Remember the formulas ever so um I'm using formulas from a different sheet.

A

um So I will paste this in here and we'll need to change this so I'll go here and then once I'm in this paso3 tab I'll copy the url and then I need to double check that this is the same. So we need to know what column true is in so it's column, one two, three, four, five!

A

Okay! So in this I will change this out with the correct sheet.

B

Oh yeah, this will mean to me.

A

Okay, not 100 sure, if it's going to work because I need to, I might need to go in and actually select that, um but this column 5 true, is right. So we'll see if this works, it might not.

A

Oh great, I did so what this sheet does. Is it worked because um this is the same.

A

um This sheet is the same as what it's titled in the 201 sheet, so it worked okay, but if you have like pass all four, then this is gonna, be this formula won't work, um so this sheet should trigger zapier, because it will only pull um new rows over to the sheet.

A

If someone, if this column reads true here so again, if I only took the epics mine would say false, so yours would be the only one that was in the sheet until I'd taken the other, and um it said true in my column, then I'd bring it over here.

A

So this this sheet should be set up and ready to go to use for zapier. You'll just need to make sure to use um this specific sheet in the larger spreadsheet, for the one that triggers it.