CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetszapier
GatorAdmiral03
GatorAdmiral03

Reputation: 69

Self Populating Cell in Google Sheets after a ZAPIER ZAP is run

I live oversees (military) and get emails when I have a package that arrives at my Post Office and is ready for pickup. I have a Zapier PARSER email account and associated ZAP setup that pulls the data from the email and updates a Google Sheets document with its shelf location, tracking number, and a few other important things from the email so the clerks can get my stuff quickly. It works great. In that same workbook, I have another sheet that I update using an app on my phone to scan tracking barcodes to when I physically pick up the package. On the first sheet I have a basic VLOOKUP function that looks for the tracking number in the scanned list to mark it off as PICKED UP (I get a lot of packages )

I have to manually go in and drag the function down when the ZAP creates a new row. it's not difficult, but i want to have it all automatic. that's what computers are for! I created a google script from the SCRIPT editor on my sheet to do it (I think)

function onEdit(e)
    {
      var row = e.range.getRow(); //Determine the Row # Just added
      var sheet = e.range.getSheet(); //Determine the Sheet, probably not needed
      var row_string = row.toString(); //Convert the Row Number to a string.
      var start_string = "=VLOOKUP(B"; //this is a fixed part of the function I need in the E column of this row.
      var end_string = ", 'Scanned Packages Fixed'!A:B, 2, FALSE)"; //This is the fixed part of the function at the end of the row.
      var set_lookup_cell = start_string.concat(row_string, end_string); //Smash the strings together to build a full function
      
      sheet.getRange(row,5).setValue(set_lookup_cell); //Put the full function of the string into the proper cell (E)
    }

I am not 100% up on how this would get called automatically or if I am missing a way to link it to the sheet itself. When I hit "run" on the code I got this:

TypeError: Cannot read property 'range' of undefined (line 3, file "Code")

I went into my sheet and just added something in the first column of a new row to see what happened, nothing did.

Any help would be super appreciated!

EDIT: Basically, I need Column E of the row to say this

=VLOOKUP(B63, 'Scanned Packages Fixed'!A:B, 2, FALSE) [Where 63 is the row#]

Upvotes: 0

Views: 141

Answers (1)

GatorAdmiral03
GatorAdmiral03

Reputation: 69

Turns out, the code above does work. I am just not patient and didn't let the sheet update and script run automatically after an update to a row.

Turns out, this isn't triggered when the zap populates the sheet... Only if I do.

Upvotes: 1

Related Questions