Complete beginner looking for answers on package tracking script

[Connor Sheffield]

Hello! 

I am building a program that is meant to track packages from two different companies. I wrote this script, but can't seem to get it to deploy and am a complete noob at this. After making the script, I tried to set it up so that other members of my org could access it, so I tried to set it as a library. Afterwards, I entered my sheets and tried to get it to load by creating a button that when pressed runs the script. Whenever I try though, it is unable to find it.  Is the issue that I need to fully flesh out the spreadsheet, in the sense that I need to create all the columns and rows? I am sure this is a dumb question for everyone here so I really thank you for your time! 

Here is the deployment ID AKfycbw3kmVSzKvQ8DeDUGW51iKYCClIUeMGtQsBak84KJmmnH1v1xdAKQCcAVWSBETHgmO3

 Here are a few screenshots

[Edward Ulle]

Too many questions about what your are trying to do.

1)  Is this a standalone app script file or a spreadsheet bound script.  By bound script I mean is it included in the same file as the spreadsheet.

2)  If a stand alone you once you've deployed it the spreadsheet that calls the library need to add it as a library.

3)  You have a function in a function.  How is myFunction called and it would have to be preceeded by the library name.

Function definition:

function myFunction() {

   function track() {

   };

}

Function access:

PackageTracker.myFunction()

But then within myFunction you need to call track.  Say for example

function myFunction() {

   function track() {

   };

   track();

}

And of course you need to provide shared access to the spreadsheet.

[Edward Ulle]

I should have noted to run myFunction() that call is made from the spreadsheet script editor, for example.

function inSpreadsheetScriptEditor() {

   PackageTracker.myFunction();

[Connor Sheffield]

Thank you so much for your response! Let me try and answer them as best I can, but this is not my area of expertise, and I apologize in advance for my lack of skills. 

I want to use this to track our office supply shipments across multiple offices, including when we start new ones. We have a master list of supplies to order depending on office size, and once we've made the order I want to be able to input the tracking info from either Fedex or UPS and have it track and give updates.  For something like this, what would you suggest I do?

 I tried to follow this video (https://www.youtube.com/watch?v=GFZyXEEDrCA&ab_channel=CommittoLearn) but I clearly failed to do so correctly. 

I really am grateful for your help and answers so far, and I really apologize for not knowing this. 

[Edward Ulle]

The video doesn't tell me much about what you have so far and where you are going with this.

1)  Is this a corporate workgroup or are these just individual Google users trying to access your spreadsheet and script?

2) I couldn't find a link to their spreadsheet and script.  Do you have these? 

[Connor Sheffield]

This would be for an individual person to use: basically the goal is for the order manager ( who is always the same person) to have access to the spreadsheet to be able to input order tracking numbers and follow along. And no, I couldn't and I reached out and got no response back, so I can't even ask them what I did wrong LOL. I tried to just follow their format, so let me leave what I have so far, including links to my spreadsheets and and script. I just tried to copy it from the video and fix mistakes as they came up. 
https://docs.google.com/spreadsheets/d/1zaIsFY7wrPyHi9yU6qcf4_hG-DjU3FLvhbsNp53mVvs/edit?usp=sharing  I think that link should work and lead to what I have been doing.

And I just want to take a second and again thank you for all your help, I genuinely am completely new at this and am just trying to make things work, and having anyone else help has been amazing. 

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/k4JKWbNu_x8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/56311c8a-c800-4007-ae5d-89744e58eee4n%40googlegroups.com.

[Edward Ulle]

I made some corrections to the script you provided.  You are missing function fetchFedEx.  You can run from the script editor or I've added a menu item so you can run from spreadsheet.  Also this assumes there is only one sheet.  I don't have any tracking number to test it with so I hope it works for you.  If possible show me any Execution log errors.

I wouldn't have done it with all the getCell(), getValue() calls but I left them so as not to confuse you.

function onOpen() {

  let ui = SpreadsheetApp.getUi();

  let menu = ui.createMenu("Package Tracker");

  menu.addItem("Run Tracker","tracker");

  menu.addToUi();

}

function track() {

  try {

    Logger .log("Start processing sheet....")

    var ss = SpreadsheetApp.getActive();

    var range = ss.getDataRange() // getting data range

    var rowEnd = range. getLastRow(); // index of last row

    var columnEnd = range. getLastColumn(); // index of last column 

    for(var i = 3; i <= rowEnd; i++) { //skip 2, ignore column titles

      Logger.log("Processing row: " + i + "/" + rowEnd);

      // getting values of all 4 columns 

      var ups = range.getCell(i, 1); // getting cell A. i represents row# and  1represents column A

      var ups_status = range.getCell(i, 2); // getting cell B. i represents row# and 2 represents column B

      var fedex = range.getcell(i, 3); // getting cell C. i represents row# and 3 represents column C

      var fedex_status = range.getCell(1, 4); // getting cell D. 1 represents row# and 4 represents column D

      // check if row has no tracking id then stop the process

      if( ( ups.getValue() == "" ) && ( fedex.getvalue() =="" ) ) {

        break; // do the next tracking number

      }

      // check if UPS has tracking id with missing status then get the status and update 

      if( ( ups.getValue() !=  "" ) && ( ups_status.getValue().toLowerCase() != "delivered" ) ) {

        var url = "https://wwwapps.ups.com/tracking/tracking.cgi?tracknum="+ups.getValue();

        ups_status.setFormula('=IMPORTXML("'+ url +'", "//*[@id=\'tt_spStatus\']")');

      }

      // check if Fedex has tracking id with missing status then get the status and update

      //  Comments out for now

      //if( ( fedex.getValue() != "" ) && ( fedex_status.getValue(). toLowerCase() != "delivered" ) ) {

        // var status = fetchFedex(fedex.getValue());  // where is function fethcFedex ?

        // fedex_status.setValue(status);

     // }

    }

  }

  catch(err) {

    Logger.log(err);

  }

}

[CBMServices Web]

Edward is correct. This script is awfully inefficient as it calls getValue on same cells many times and the number of calls to spreadsheet functions imbedded inside a for loop will make this very slow to run if the spreadsheet grows to any decent size.

I watched the demo also, the fetchFedex function was not shown, but it appeared they may have had it if they had bothered to scroll down. The YouTube video is pretty badly done..

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d7d682e3-2837-47dc-962c-d7b9cfc36d10n%40googlegroups.com.

[Edward Ulle]

Note that you don’t need to deploy. This can be used as a shared spreadsheet with a bound script. Any one with the link and edit access can edit the sheet and run the script.

[Connor Sheffield]

Yeah, I don't know enough about this kind of thing to be able to make it better so I am super grateful for everything people have done for me.  That video was TRASH and I agree, he said he would leave the link in the video to the sheet and script and did not, so I just tried to do the best I could, which is like F+ at best LOL 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3WX9Gn0sp-GBc1RWOFqmzm%3DHOEz2_G6KXweRv3zAwqO8g%40mail.gmail.com.

[Edward Ulle]

You could try the UPS with the script I provided and let us know if it works.

[Edward Ulle]

Found one more typo

var fedex = range.getCell(i, 3)

[Connor Sheffield]

Man, I know it's not a lot but tonight on our call I am absolutely going to say thank you and tell my staff this only happened because you were willing to help me so much. 

Here is what I did and here are the results.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1dabac6f-2f47-4067-91b4-acd12572df62n%40googlegroups.com.

[Edward Ulle]

My bad in the menu item i put "tracker" but the function name is "track"  change "tracker" to "track"

[Edward Ulle]

The UPS method in the script is brute force method.  Both FedEX and UPS have an API but it requires you to set up an account, get some authentification keys and then you can make API calls to get tracking info.  But that's a lot more than a one liner.

[cbmserv...@gmail.com]

Fedex seems to have a nicely documented api. Here is the link to it if you feel up to programming that in.

 

Track API Documentation | FedEx Developer Portal

 Here are a few screenshots

 

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/k4JKWbNu_x8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/56311c8a-c800-4007-ae5d-89744e58eee4n%40googlegroups.com.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d7d682e3-2837-47dc-962c-d7b9cfc36d10n%40googlegroups.com.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/k4JKWbNu_x8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1dabac6f-2f47-4067-91b4-acd12572df62n%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f492770e-9a10-4700-a2d8-c443240216e3n%40googlegroups.com.

[Connor Sheffield]

So should it look like this then?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/002001d89ed8%24c2c8e560%24485ab020%24%40gmail.com.

[Edward Ulle]

Yes, does it run without error?

[Connor Sheffield]

If i it to onOpen with the dropdown menu next to run then it gives me the error you saw, if I set it to track it goes like this. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/dac01f64-6d4b-48eb-8358-49796bc59da9n%40googlegroups.com.

[Edward Ulle]

If you didn't close and reopen or rerun onOpen from the script editor its still the old menu "tracker".  Otherwise from script editor track looks like it ran okey.  Do you see the status in the spreadsheet?

[Connor Sheffield]

Thank you for taking all the time to help out and explain this to me! 

I went into Apps Script, made sure it was all saved and closed and reopened the program. I went ahead and found a package we're shipping via UPS, and entered the info in. I made sure on the home page that the script was attached to the right spreadsheet. 

The first screenshot is from the debug menu for onOpen and the second is from clicking run on it. The third and fourth are the debug and run for track. I feel like I must just be making some kind of dumbass mistake here and I cannot figure it out, and the last few are the results and double checking the script. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bd8808e0-5c0c-4436-b503-ee2218d75d0fn%40googlegroups.com.

[cbmserv...@gmail.com]

Connor,

 

The function name is track. So when you assign script to the button, use the name track (don’t use Tracker).

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAAhg9kUmJe-y0wB86moR%2BRBUdXG5_2bM%3DtBbF-a%2BuF3pkbZjGQ%40mail.gmail.com.

[Edward Ulle]

First screen.  If you are running debugger and are breakpoint at first line nothing has been defined yet.

Screen 2 & 3.  The function should be "track"

Screen 4 & 5.  You are running onOpen in debug.  I think that won't work.

[Connor Sheffield]

Wow, that should have been so clear, I am so sorry. This is my first time (literally have never tried this before but when your bosses say do it or else am I right?) trying to do something like this, and so I am sorry for all the beginner's mistakes I made to get here! Y'all have been so helpful!! 
I went ahead and modified that and tried to track a package! And it sorta works! 

I input a tracking number that I know is valid, hit run and it came back with this. I double checked the tracking info and went to UPS and tracked it there and it shows it's "on it's way" but the column fills in with N/A, any ideas what I have done wrong again? And again, I am so thankful for all this advice and teaching, hopefully when they eventually ask me to add USPS to this I will be better LOL.  

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/003f01d89ee2%243923bab0%24ab6b3010%24%40gmail.com.

[Edward Ulle]

If I had a valid tracking number for FedEx there is UrlFetch() and Utilities.parseXML() that could be used to extract the status.  I did it for an old tracking number but it was delivered long ago and so the fedex status was not available.  But please don't give out anything like that on this web site.

[Edward Ulle]

Like I said the UPS method is brute force using IMPORTXML.  I don't know if the query works.  I get NA for an old UPS tracking number but I thought it was because it was so old.

[Connor Sheffield]

No thank you for catching that no idea why I sent the number, I will try and figure out why it isn't returning a date!

[Edward Ulle]

Copy this formula somewhere in the spreadsheet with plenty of space to right and below and you will see what IMPORTXML returns

=IMPORTXML("https://wwwapps.ups.com/WebTracking/track?track=yes&trackNums=your-tracking-number-here&requester=ST/","*")

There is no string "@id=" in it so the query return nothing.