Google Apps Scripts for Gmail and Parser - Minderlaw 20191219G
Budget: $30 – $250 USD
Looking to have 3x separate (although related) Google Apps Scripts on the spreadsheet at https://drive.google.com/drive/folders/1s_4ZQTQP7ZenvrvAxRyUG7EVEEr2Jvy1 . Column A to Column I are user inputs. The columns will sometimes have blank data. Only Column I (Application No.) will always have data.
SCRIPT1 - PARSER SCRAPER
The first script is a parser script. Row 2 shows a sample input and Row 3 shows the output for Row 2. For each Row with data in Column I (Application No.), the parser script goes to the URL https://tsdr.uspto.gov/#caseNumber=[Application No.]&caseSearchType=US_APPLICATION&caseType=DEFAULT&searchType=statusSearch
For example, in Row 2, the parser would scrape from https://tsdr.uspto.gov/#caseNumber=79262491&caseSearchType=US_APPLICATION&caseType=DEFAULT&searchType=statusSearch
Please see PDF for the 3x fields to be scraped, Column J (Trademark No.), Column K (International No.), Column L (Mark), and Column L (RegDate in yyyy-MM-dd). Note that the USPTO website blocks bulk parsing. So if possible, please add a time delay and random user agents between scraping each URL.
SCRIPT2 - GOOGLE CALENDAR
Script2 is already in Код.gs. If Script1 and Script3 do not interfere with Script2, then no changes need to be made to Script2. Script2 basically schedules events in Google Calendar based on the dates shown in the spreadsheet.
SCRIPT3 - GMAIL DRAFT
Script3 creates a draft email in Gmail based on the data from the spreadsheet. Script3 should not send the email.
The template email is shown below, including HTML tags. Column K (International No.) might not have data, so the template should adjust the sentence if Column K is blank. For example, instead of:
On [[Column L (RegDate)]], the USPTO issued the attached Trademark Certificate for US Trademark Registration No. [[Column J (Trademark No.)]], issued from US Trademark Application No. [[Column I (Application No.)]], based on International Registration No. [[Column K (International No.)]] (relating to [[Column G (Title)]]).
It would be:
On [[Column L (RegDate)]], the USPTO issued the attached Trademark Certificate for US Trademark Registration No. [[Column J (Trademark No.)]], issued from US Trademark Application No. [[Column I (Application No.)]] (relating to [[Column G (Title)]]).
EMAIL SUBJECT TEMPLATE
[[Column D (Client)]] [[Column E (Minder Ref.)]] [[Column F (Other Ref.)]] - Trademark Certificate
EMAIL BODY TEMPLATE
See TXT file.
Candidates should confirm that they have Google Apps Scripts experience. This project should not be done using Python, VBA, or other scripts outside of Google Apps Scripts. Thank you.
SCRIPT1 - PARSER SCRAPER
The first script is a parser script. Row 2 shows a sample input and Row 3 shows the output for Row 2. For each Row with data in Column I (Application No.), the parser script goes to the URL https://tsdr.uspto.gov/#caseNumber=[Application No.]&caseSearchType=US_APPLICATION&caseType=DEFAULT&searchType=statusSearch
For example, in Row 2, the parser would scrape from https://tsdr.uspto.gov/#caseNumber=79262491&caseSearchType=US_APPLICATION&caseType=DEFAULT&searchType=statusSearch
Please see PDF for the 3x fields to be scraped, Column J (Trademark No.), Column K (International No.), Column L (Mark), and Column L (RegDate in yyyy-MM-dd). Note that the USPTO website blocks bulk parsing. So if possible, please add a time delay and random user agents between scraping each URL.
SCRIPT2 - GOOGLE CALENDAR
Script2 is already in Код.gs. If Script1 and Script3 do not interfere with Script2, then no changes need to be made to Script2. Script2 basically schedules events in Google Calendar based on the dates shown in the spreadsheet.
SCRIPT3 - GMAIL DRAFT
Script3 creates a draft email in Gmail based on the data from the spreadsheet. Script3 should not send the email.
The template email is shown below, including HTML tags. Column K (International No.) might not have data, so the template should adjust the sentence if Column K is blank. For example, instead of:
On [[Column L (RegDate)]], the USPTO issued the attached Trademark Certificate for US Trademark Registration No. [[Column J (Trademark No.)]], issued from US Trademark Application No. [[Column I (Application No.)]], based on International Registration No. [[Column K (International No.)]] (relating to [[Column G (Title)]]).
It would be:
On [[Column L (RegDate)]], the USPTO issued the attached Trademark Certificate for US Trademark Registration No. [[Column J (Trademark No.)]], issued from US Trademark Application No. [[Column I (Application No.)]] (relating to [[Column G (Title)]]).
EMAIL SUBJECT TEMPLATE
[[Column D (Client)]] [[Column E (Minder Ref.)]] [[Column F (Other Ref.)]] - Trademark Certificate
EMAIL BODY TEMPLATE
See TXT file.
Candidates should confirm that they have Google Apps Scripts experience. This project should not be done using Python, VBA, or other scripts outside of Google Apps Scripts. Thank you.