deleted-U072JS2NHRQ
2024-11-21 11:08:06

Hey @Dustin Surwill are you super familiar with how google cloud console works and stuff?

Dustin Surwill (dsurwill@shield-legal.com)
2024-11-21 11:09:59

I am familiar with the parts I use. (Cloud Functions, Cloud Run, BigQuery, Cloud SQL, IAM)

Dustin Surwill (dsurwill@shield-legal.com)
2024-11-21 11:15:41

What do you need help with?

deleted-U072JS2NHRQ
2024-11-21 11:18:50

So I'm trying to authenticate gmail access through python, but whenever it tries to have me sign in, it gives me this error in the screenshots. So in Google Cloud Console, the project 'Tort Intake Professionals' is under the organization 'No Organization' and not under the organization 'tortintakeprofessionals.com' so it's not recognizing me and edward when we try and use our gmail API's to access our gmail or the reporting team email.

image.png
image.png
deleted-U072JS2NHRQ
2024-11-21 11:21:10

The solutions I found are: • Make the OAuth Consent External instead of what it currently is, which is Internal • or possibly move the project under the 'tortintakeprofessionals.com' organization since it will recognize our @tortintakeprofessionals.com emails as internal , but the caveat with that is idk if it'll recognize anyone that has a @ shield-legal email address but I also don't know if there's another way around this or not, so I wanted to ask you or mcfadden

Dustin Surwill (dsurwill@shield-legal.com)
2024-11-21 11:21:51

Can I get the code you are using to test with?

deleted-U072JS2NHRQ
2024-11-21 11:22:12

```from google.oauth2.credentials import Credentials from googleauthoauthlib.flow import InstalledAppFlow from googleapiclient.discovery import build import os

Scopes for accessing Gmail

SCOPES = ['https://www.googleapis.com/auth/gmail.readonly']

def authenticate_gmail(): """ Authenticate with Gmail API and return the service object.

Returns:
    service (Resource): Authorized Gmail API service object.
"""
creds = None
token_file = r'C:\Users\Aidan\OneDrive - Shield Legal, LLC\Documents\Data Warehouse Credentials\gmailtoken.json'
credentials_file = r'C:\Users\Aidan\OneDrive - Shield Legal, LLC\Documents\Data Warehouse Credentials\Gmail.json'

# Check if the token file exists (user has logged in before)
if os.path.exists(token_file):
    creds = Credentials.from_authorized_user_file(token_file, SCOPES)

# If there are no valid credentials, prompt for login
if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
        creds.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file(credentials_file, SCOPES)
        creds = flow.run_local_server(port=0)
    # Save the credentials for the next run
    with open(token_file, 'w') as token:
        token.write(creds.to_json())
    print("Authentication successful. Token saved.")

# Build the Gmail service object
service = build('gmail', 'v1', credentials=creds)
return service

Call the authenticate function to get the Gmail service

service = authenticate_gmail()

Test by listing Gmail labels

results = service.users().labels().list(userId='me').execute() labels = results.get('labels', []) print("Labels:") for label in labels: print(label['name'])```

Dustin Surwill (dsurwill@shield-legal.com)
2024-11-21 11:59:55

Try now

deleted-U072JS2NHRQ
2024-11-21 12:01:26

same thing, it was like 'pick a google account to log into now' and then when I clicked mine, it said BLOCKED 😭

Dustin Surwill (dsurwill@shield-legal.com)
2024-11-21 12:05:41

again please

deleted-U072JS2NHRQ
2024-11-22 11:34:56

btw, I got the gmail thing to work now

Dustin Surwill (dsurwill@shield-legal.com)
2024-11-22 11:35:24

how?

deleted-U072JS2NHRQ
2024-11-22 11:37:34

The User Type was switched to External.

I tried switching it to External yesterday with Nick and Ahsan, and it didn't work, so we switched it BACK to Internal. but when I came in today, it was switched back to External and was working, so I haven't touched it again lol

image.png
deleted-U072JS2NHRQ
2024-11-22 11:38:20

call me IT lol

Dustin Surwill (dsurwill@shield-legal.com)
2024-11-22 11:38:26

I made it external yesterday and added you as a test user. Maybe it just took time to populate through Googles system

👍:skin_tone_3: deleted-U072JS2NHRQ
deleted-U072JS2NHRQ
2024-11-22 11:38:54

yeah when I was googling things yesterday some people said it takes like a couple hours/days to work. At LEAST it works now, thank you!

deleted-U072JS2NHRQ
2024-11-22 11:39:21

if you guys ever need me to be the guinea pig with this kinda stuff lemme know lol

👍 Dustin Surwill
deleted-U072JS2NHRQ
2024-12-05 12:12:51

Hey Dustin! Does the BigQuery table refresh EVERYTHING every 15 minutes? Does it just take longer for the 1st initial pull than to refresh everything? I know you guys had to build the layout/schema/whatever it's called first so that took time, but just wanted to confirm

Dustin Surwill (dsurwill@shield-legal.com)
2024-12-05 12:13:30

it updates all the changes from the last hour every 15 minutes

deleted-U072JS2NHRQ
2024-12-05 12:15:10

that's fuckin neat! finally, I can use my decaying sql knowledge, but thanks again!

deleted-U072JS2NHRQ
2024-12-05 12:25:41

do you know if there's a connex database too? for calls?

Dustin Surwill (dsurwill@shield-legal.com)
2024-12-05 12:26:16

There is but there are problems with it, @deleted-U06GD22CLDC know more

deleted-U072JS2NHRQ
2024-12-05 12:26:28

gotcha, thank you!

deleted-U072JS2NHRQ
2024-12-09 12:25:58

Do you happen to know if we're able to upload like Excel data to your BigQuery table? to get the e-sign dates/data would it be easy or too much work to have the api pull a report and just upload the data to an e-sign table? idk how much you're working on it, but just seeing if there's anything I could do to help

deleted-U072JS2NHRQ
2024-12-09 12:27:19

from my knowledge, I'd have to look in lead_history for e-sign to find the e-sign date currently, correct?

Dustin Surwill (dsurwill@shield-legal.com)
2024-12-09 13:59:34

yes you have to look into lead_history right now. I am looking into/working on 2 seperate things that would make that lookup much faster

👍:skin_tone_3: deleted-U072JS2NHRQ
deleted-U072JS2NHRQ
2024-12-09 14:06:34

Gotcha, perfect, thank you, I appreciate you!

deleted-U072JS2NHRQ
2024-12-18 10:00:38

Hey Dustin, quick question, I wrote some python code that's grabbing a report of all lead id's and pulling the contact id for all of them, but it's taking like forever. My plan was to update the Vitals with the contact id for you guys so it's easier for you guys, but I'm wondering, am I able to run multiple different python files and have multiple terminals open accessing lawruler?

Dustin Surwill (dsurwill@shield-legal.com)
2024-12-18 10:27:15

No unless you use multiple LR accounts. If you do that with 1 account you WILL lock that account. I do not know of an ask to add the contact ID to the vitals. I have a DB that has all leads and their contact id

deleted-U072JS2NHRQ
2024-12-18 10:58:04

it's kind of a personal project, to just make it easier on you guys honestly, it gives me a chance to like show a little bit more of what I can do honestly

deleted-U072JS2NHRQ
2024-12-18 10:58:41

do you know which table? I checked the 'lead' table that has the contact id column, but only some of them were filled out, the majority was blank/null

deleted-U072JS2NHRQ
2024-12-18 11:01:42
 
  contactid,
  id
FROM
  tort-intake-professionals.lr_data.lead AS l
WHERE l.contactid IS NOT NULL
deleted-U072JS2NHRQ
2024-12-18 11:02:24

only 23k have it

image.png
Dustin Surwill (dsurwill@shield-legal.com)
2024-12-18 11:27:19

Thats because there was a bug that I fixed Monday. I will be running a script to fix the rest over the weekend. I do not know how to access the vitals

👍:skin_tone_3: deleted-U072JS2NHRQ
deleted-U072JS2NHRQ
2024-12-18 11:32:44

the vitals are just a form question, they're the same for all the leads, I have the id, question and lawrulerfield if you want them, but would you be willing to let me update the lawruler vitals once your db is updated? lol I want the practice/experience for this if that's ok with you

deleted-U072JS2NHRQ
2024-12-18 11:33:04

it's just using set_answer api to update the fields

Dustin Surwill (dsurwill@shield-legal.com)
2024-12-18 11:36:24

Not up to me, we probably will not be using that field

👍:skin_tone_3: deleted-U072JS2NHRQ
deleted-U072JS2NHRQ
2024-12-18 11:42:58

oh cuz your script will be updating the changes too so any new ones will be pulled by the db and updated in the table huh

deleted-U072JS2NHRQ
2024-12-18 11:43:07

ok yeah no worries then! i was just curious

deleted-U072JS2NHRQ
2024-12-20 11:49:17

ApiReport/GetCustomReport/ ApiContacts/GetIntakeContacts/ ApiIntakeForms/SetAnswer/

Interval: one time

Statistics & Reporting - contact id updating for CLJ manual reconciliation

deleted-U072JS2NHRQ
2024-12-30 15:14:20

Do you care if I add descriptions to the views in bigquery? everyone else is going to be learning sql and using bigquery soon and I think it'll be helpful to add a little description. Also, do you care if we create our own views? not everyone, but like I'm going to create a couple views that pertain to the reports that we pull, so it'll be easier than joining 3-5 different tables

Dustin Surwill (dsurwill@shield-legal.com)
2024-12-30 15:14:47

That should all be fine

👍:skin_tone_3: deleted-U072JS2NHRQ
Dustin Surwill (dsurwill@shield-legal.com)
2024-12-30 15:15:48

Just beware that currently any lead that has not updated in a few weeks will not have the contact id to match to the contacts table. we are working with LR to get the data to fix it.

deleted-U072JS2NHRQ
2024-12-30 15:17:21

no worries! thank you

deleted-U072JS2NHRQ
2025-02-03 19:59:46

Secondary Dates that were completed today 2.3.25

Secondary Date Push 2.3.25 - Secondary Dates.csv
deleted-U072JS2NHRQ
2025-02-05 12:15:17

Hey Dustin, here is the raw lawruler report for the benchmark, there's some updates on it for this morning

Flatirons_Benchmark_Data_2.5.25-Custom Report-2025-02-05.xlsx
Dustin Surwill (dsurwill@shield-legal.com)
2025-02-05 12:15:29

Thanks

deleted-U072JS2NHRQ
2025-03-17 16:06:48

it's the one "PFSFullMacro" - if you open it in VBA you can see the column adjustments/additions

PERSONAL.XLSB
Dustin Surwill (dsurwill@shield-legal.com)
2025-03-17 16:25:35

Can I get a copy of the latest LR report for this data as well?

Dustin Surwill (dsurwill@shield-legal.com)
2025-03-17 16:27:26

And an example output of the macro running?

deleted-U072JS2NHRQ
2025-03-17 16:30:26

I'm having Martin send it to you

deleted-U072JS2NHRQ
2025-03-17 16:30:43

use the macro on the unedited version and you'll get the output , just make a duplicate save

deleted-U072JS2NHRQ
2025-03-17 16:31:29

i can send you the 'macro'ed' version tomorrow but i gotta head out rn

👍 Dustin Surwill
deleted-U072JS2NHRQ
2025-04-02 12:22:03

@Dustin Surwill do you happen to remember which table the vitals are in in lr_data

Dustin Surwill (dsurwill@shield-legal.com)
2025-04-02 12:23:03

It would be with the questions and answers which are case_question and lead_question

👍:skin_tone_3: deleted-U072JS2NHRQ
deleted-U072JS2NHRQ
2025-04-02 13:18:05

The 'Case:' vital question is not in the database, there are others that are similar to the 'Case:' vital, but they are like 'Case #' or 'Case ID:' which are not the same as the vital, but those are part of the form questions of specific campaigns

Dustin Surwill (dsurwill@shield-legal.com)
2025-04-02 13:21:00

Can I get an example leadid?

deleted-U072JS2NHRQ
2025-04-02 13:21:28

409616

Dustin Surwill (dsurwill@shield-legal.com)
2025-04-02 13:34:48

I cannot find that in the DB and when I looked at the LR API there does not seem to be a way to get the vitals. They are also not returned as questions

deleted-U072JS2NHRQ
2025-04-02 13:48:30

i believe you use getintakeform and pick a lead id and then for headerid you put 0 in order to get the vitals, but i'm not 100% sure without being able to run it myself, but there IS a way to get them because the vital has a custom id (c-25451) so it's a question

deleted-U072JS2NHRQ
2025-04-02 13:48:37

so it'll either be getquestionlist or getintakeform

Dustin Surwill (dsurwill@shield-legal.com)
2025-04-02 13:49:49

getquestionlist only gets the questions. headerid 0 does not return any questions

deleted-U072JS2NHRQ
2025-04-02 13:50:38

try getquestionlist and for the casetypeID put 0

deleted-U072JS2NHRQ
2025-04-02 13:51:14

there IS 100% a way to get the vitals, i've gotten them before

Dustin Surwill (dsurwill@shield-legal.com)
2025-04-02 13:58:51

they exist in getquestionlist but that does not give the answer

Dustin Surwill (dsurwill@shield-legal.com)
2025-04-02 13:59:47

nvm. the question i was looking at is on the csp only tab not the vitals

deleted-U072JS2NHRQ
2025-04-02 14:07:11

if you use getintakeform, use headerid 2036

deleted-U072JS2NHRQ
2025-04-02 14:07:17

that should give the same data

deleted-U072JS2NHRQ
2025-04-02 14:07:21

but with the answer i believe

deleted-U072JS2NHRQ
2025-04-02 14:42:33

so I found out getintakeform would only give you the answer for a specific lead id, so it'll give you the answer for the vitals, but you'd have to loop through each and every lead ID in order to get the answers, and that's probably just not possible with how lawruler is right now

I also thought of another way we can get the vitals data from lawruler, which would be getcustomreport, cuz we have a layout with the vitals in it, so you can just pull it that way, but you'd have to choose case types in order to pull it.

still do the ticket though, cuz they need to make a better way to get vitals either way though

Dustin Surwill (dsurwill@shield-legal.com)
2025-04-02 18:08:25

Who is currently in the group <a href="mailto:reportingautomation@tortintakeprofessionals.com">reportingautomation@tortintakeprofessionals.com</a>?

deleted-U072JS2NHRQ
2025-04-03 07:54:50

it's just another email that we're using for automating things, so like for example we get monday reports sent to that email so that we can automate it

Dustin Surwill (dsurwill@shield-legal.com)
2025-04-03 10:12:59

So is it an email address or a distribution list?

deleted-U072JS2NHRQ
2025-04-03 10:42:21

email address

Dustin Surwill (dsurwill@shield-legal.com)
2025-04-03 10:48:39

Does anyone log into that account?

deleted-U072JS2NHRQ
2025-04-03 10:56:42

yes

deleted-U072JS2NHRQ
2025-04-03 10:56:48

cuz it's used for some of our automations

deleted-U072JS2NHRQ
2025-05-21 11:50:30

So this is what brittany told me:

image.png
deleted-U072JS2NHRQ
2025-05-21 11:50:54

but after checking, one of them was for Radio

deleted-U072JS2NHRQ
2025-05-21 11:50:57
image.png
deleted-U072JS2NHRQ
2025-05-21 11:51:24

the one that's NOT labeled with the -Automation was originally for radio leads I guess? idk for sure , some more investigation will be needed

Dustin Surwill (dsurwill@shield-legal.com)
2025-05-21 11:58:09

Here is the activity log of all the case type changes for those campaigns

239.csv
406.csv
Dustin Surwill (dsurwill@shield-legal.com)
2025-06-26 16:24:45

Is your code that uses the service accounts in GitHub?

Dustin Surwill (dsurwill@shield-legal.com)
2025-06-30 12:41:01

https://cloud.google.com/docs/authentication/application-default-credentials

Google Cloud
How Application Default Credentials works  |  Authentication  |  Google Cloud
Original URL: https://cloud.google.com/docs/authentication/application-default-credentials