Experienced Appscript / Sheets Developer

I’m looking to have a dashboard built on Google Sheets that covers many complex functions related to order mapping using Google Sheets, Appscript, and Order Tracking. I had a previous dev working on the project, and he put in roughly 40 hours into coding the fundamentals, but it was out of his scope as a developer.

The qualified candidate must be proficient and skilled with Appscript and automation platforms (Zapier, Make etc), and able to create workarounds with e-mail formats. You will not be working from ground zero – my previous dev has already created a base for this, and you will be working to take over where he left off. He will be able to walk you through the code and nuances and you will be briefed. Payment will be a set amount for the project, and be made for full completion of what I’m looking for. I will choose the candidate based on a fair hourly rate (for after-sales service + future projects) and proficient experience in being able to handle the project.

Intro to the project:

# Hyperduty Dashboard
– The core task of the dashboard is to convert emails from gmail account to rows in Hyperduty Dashboard sheets
– The Google sheets file has 2 sheets: Dashboard and Search.
– Search sheet is easy, it’s more or less done.
– Dashboard has Appscript code which can be viewed through Extensions -> Appscript. This is the code which is supposed to do the core task.

## Appscript Code
– deleteAllRowsExceptFirst clears dashboard sheets. This is used when starting again. Some code changes require deleting everything and repopulating the sheets.
– fillUpDashboard functions are used to populate the rows. The functions are ran in the following order:
>_confirm
>_cancel
>_qla_cancel
>_ship
>_deliver
>_return
>_refund
– They convert emails into either new rows (confirm) or edit existing rows (all the rest).
– Amazon orders do not necessarily go through all steps in this process, but this is the order in which the emails are received.
– Each function (except _deliver) operates on a specific email type which is labelled in Gmail. For example: fillUpDashboard_cancel operates on emails labelled with “Amazon/amz Order Cancellation”
– each function has the basic structure:
> get all email threads with the following label
> extract all emails
> for each email, extract the order number. Also extract other needed info depeding on the function.
> for _confirm, create a new row. for the rest, find the row with the same extracted order number and edit it.

## Running the Code
– There is a 2-step way to run the code to fulfill the core task. First is to run the functions in the order above. 2nd is to setup a timed trigger that checks for new emails (say, every 1 hour) and add/edit rows based on the new emails received. The first step is to add the old emails to the dashboard. The 2nd step is to add newly received emails in near real-time.

## Problems
– The maximum execution time for an appscript function is 6 minutes. In the case of _confirm, emails are so many that it doesn’t fit the time limit. My solution was to log the index of iteration and start from the last index during the next run.
– The maximum threads that getThreads() can retrieve is 500. This is a problem for _confirm, again for the same reason. A solution can be found here: Click to view/questions/46096110/250-500-threads-limit-in-gmailapp-with-google-apps-script (unimplemented). This has to be solved to get all emails that exist.
– Amazon emails are intentionally incosistent in format. This is to prevent automation (like we do). Looking at the code, you will notice handling of many cases and checking for null. That is because of this. Some dashboard cells are unfilled because the case is unhandled.
– The tracking number isn’t in email. It’s found when logging into amazon account. To automate tracking # retrieval, you have to use selenium. This is what a complete solution looks like: Click to view/gbrodman/order-tracking
– If you decide to do browser automation for tracking #, caution must be taken to not get the accounts banned. So know the reasons why amazon accounts are banned. Maybe you’ll need a US VPN, use undetected-chromedriver and make browser profile for each amazon account in selenium. See the github repo for guidance. This task is hard because it requires alot of testing and accounts will be banned in the process. So maybe you’ll have to create new amazon accounts.
– once a way is decided on how to get tracking number (either manual input or browser automation), you can work on the receipt emails and [payout, gross, net] columns. Then the task is complete.

If you read the intro and made it this far, please respond with similar projects you’ve worked with and the word “BUYING GROUPS” somewhere in your response so I know you read the full requirements.