0

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.

  • Welcome to Web Applications. This question is not a good fit for this site. Please follow the tour and checkout How to Ask – Rubén Aug 20 at 23:44
  • "I am not but a mere peeon with little to no knowledge of coding." FedEx have an API that is available to developers. However, might I respectfully suggest that if you have little or no coding skills, and given that you have a solution already, this would be a technically complicated, complex and time consuming venture to take on. The topic "How to incorporate UPS, FED-EX and USPS tracking numbers in Google Sheets?" is worth reading - if that material makes sense then you have a short-cut to a solution, otherwise it is a very long road to hoe. – Tedinoz Aug 23 at 12:01
  • Welcome. Have you read How to incorporate UPS, FED-EX and USPS tracking numbers in Google Sheets? on StackOverflow? – Tedinoz Aug 23 at 12:02
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.