Google-sheets – FedEx Tracking in Google Sheets

google sheetsgoogle-appsgoogle-apps-scriptgoogle-drivegoogle-sheets-dates

I work for a company that sends packages out daily. We track the numbers in a Google sheet that populates via a Google form we use.

Recently we have had some packages go missing and I have been tasked with setting something up that allows us to watch the packages and make sure they are not getting lost in an effort to be proactive.

My thoughts were to do some sort of status updates with the tracking number, currently using lovelyAPI's package tracker add on, but I would like something custom if possible.

enter image description here

Column x, y, z, aa, and ab are all populating through the package tracker and I have a formula to hyperlink the FedEx site and insert the value in F.

Is there a script that can do the same thing for me using the FedEx API?

I would also like to go a step further and have some sort of conditional formatting that would highlight the row in a certain color if the date I have in column A is older than 2 days and column Y does not return "delivered" or something like that. Can anyone help me out with this?

Trying to automate the whole thing but I am not but a mere peeon with little to no knowledge of coding.

Best Answer

You want to highlight an entire row where the status is not "Delivered" and the date is more than two days old.

The normal process for conditional formatting applies with a couple of exceptions:

  • create a helper cell containing the formula =today(). Cell "E1" in the example below.
  • use a custom formula.
  • put a dollar $) sign in front the cell reference to make the formula apply to the whole row.
  • use AND so that both of the conditions must be met if the formatting is to apply.

In the example below, the custom formula is =and($C2<$E$1-2,$A2<>"Delivered").

  • $C2<$E$1-2 - compares the date in column C with the current date. The adjustment for "older than 2 days " is achieved by $E$1-2.
  • $A2<>"Delivered" - tests the status and will highlight any row where the value in Column A is not "Delivered".

Test data

test data

Custom Formula

Custom formula

Related Topic