Google App Script to Scrape Data Minderlaw 20210221Y

For this Google App Script project, please see spreadsheet https://drive.google.com/drive/u/0/folders/1J31-Uy3229RvQkiXwFd8KrxdwiVlMKWh and the attached PDFs. In Worksheet Trademarks, Column E (Application) are a list of Application Numbers. The script logic is listed below.

1. For each Row, go to the URL https://tsdr.uspto.gov/#caseNumber=[Application Number]&caseSearchType=US_APPLICATION&caseType=DEFAULT&searchType=statusSearch . For example, the URL for Row 2 would be https://tsdr.uspto.gov/#caseNumber=4469429&caseSearchType=US_APPLICATION&caseType=DEFAULT&searchType=statusSearch . The attached PDFs show the resulting webpage at https://tsdr.uspto.gov/#caseNumber=4469429&caseSearchType=US_APPLICATION&caseType=DEFAULT&searchType=statusSearch

2. If Worksheet Trademarks Column F (Registration) is blank, fill in the Registration Number. Note that some Applications do not have Registration Numbers.

3. If Worksheet Trademarks Column G (Registration Date) is blank, fill in the Registration Date in the format yyyy-MM-dd. Note that some Applications do not have Registration Dates.

4. If Worksheet Trademarks Column H (Mark) is blank, fill in the Mark. Note that some Applications do not have Marks.

5. For Worksheet Trademarks Column I (TM5 Common Status Descriptor), overwrite the TM5 Common Status Descriptor. Even if Column I is not blank, overwrite the field with the current TM5 Common Status Descriptor.

6. For Worksheet Trademarks Column J (Status), overwrite the Status. Even if Column J is not blank, overwrite the field with the current Status.

7. For Worksheet Trademarks Column K (Next Filing), overwrite the Next Filing in the format yyyy-MM-dd. Even if Column K is not blank, overwrite the field with the current Next Filing date. Note that the Next Filing date is under the Maintenance tab.

8. For Worksheet Trademarks Column L (Filing Type), overwrite the Filing Type. Even if Column L is not blank, overwrite the field with the current Filing Type. Note that the Filing Type is under the Maintenance tab.

9. Note that the USPTO website throttles web traffic. So the script should include some time delay between the USPTO searches. Because Google Apps Scripts usually timeout around 5 minutes, the script might have to add an additional column (Column N) that tracks which Row the script last processed. In other words, after the script completes processing Row 2, it should mark an X in Column N so that it does not process Row 2 the next time. 

Candidates should confirm that they have Google Apps Script experience. Thank you.