Google Sheets App Script - Menu Item Link Purchase Order # to Packing Slip #

Question

I am having trouble wrapping my mind around this problem.

I have a Sheet with customer purchase orders.

Here is a very dumbed down sheet example here: https://docs.google.com/spreadsheets/d/151h1XjB98NOBnO0otNaql3ASjK84CccZZ399dX4BMBM/edit?usp=sharing

I need a way to link a Packing slip # to a matching list of customer Purchase order #'s on an order sheet.

using some sort of copy cell value "Packing Slip!G2" where "orders!C:C" = "Packing Slip!G5" to "orders!D:D"

Do you think this is feasible?

Thank you.

been smashing around, I trimmed out stuff that I was mussing with and will leave this here for the morning hopefully I can make better sense.

    function linkPackNumToPo() {
var activeSheet = SpreadsheetApp.getActive();
var ps = activeSheet.getValue("G2");
var po = activeSheet.getValue(G5);

}

So I can not figure out a way to do a similar operation to vlookup using app script.

So now I am thinking I need to write to a separate sheet a single record for each Purchase Order # being linked to the Packing Slip # and/or Sales Order # also.

I am using the following code to copy the Date, Company, Purchase Order, Packing Slip, to another sheet to act as a key to matching Purchase orders to packing slips.

Also added some clear contents to reset the packing slip, Then routine to auto increment the Packing Slip #, using L3 for the key value.

    function clearPackingList() {
      var ss = SpreadsheetApp.getActive();
      var sheet = ss.getSheetByName("PackSlip");
      var source = sheet.getRange("N3:Q3");
      var target = ss.getSheetByName("OrderKey");
      var values = source.getValues();
      target.appendRow(values[0]);

    sheet.getRange('C6').clearContent();
    sheet.getRange('C9').clearContent();
    sheet.getRange('C10').clearContent();
    sheet.getRange('B11').clearContent();
    sheet.getRange('B12').clearContent();
    sheet.getRange('H6:H8').clearContent();
    sheet.getRange('F11').clearContent();
    sheet.getRange('G11').clearContent();
    sheet.getRange('H14:H77').clearContent();
   var cell = sheet.getRange("L3");
  var cellValue = cell.getValue();
  cell.setValue(cellValue + 1);
    };

I need to add a menu item next and figure out how to write to the ORDERS sheet matching Purchase orders to the Packing slip,

Also working on figuring out how to print a Range of cells.

I am used to using simple sheet functions, but I do not want the resulting values to be a live calculation, but rather set. I was thinking there may be a way to use getValue (packingslip cell) to setValue (packingslip value) to copyTo( range, cells that match next to customer PO# in Order column) — Mars

Mars Mars · Accepted Answer · 2016-01-23T20:55:30

Okay so I have a working 'kludge'

    function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Submit Packing List', 'menuItem1')
      .addToUi();
}

function menuItem1() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("PackSlip");
  var source = sheet.getRange("N3:Q3");
  var target = ss.getSheetByName("psKey");
  var values = source.getValues();
  target.appendRow(values[0]);

    sheet.getRange('C6').clearContent();
    sheet.getRange('C9').clearContent();
    sheet.getRange('C10').clearContent();
    sheet.getRange('B11').clearContent();
    sheet.getRange('B12').clearContent();
    sheet.getRange('H6:H8').clearContent();
    sheet.getRange('F11').clearContent();
    sheet.getRange('G11').clearContent();
    sheet.getRange('H14:H77').clearContent();
  var cell = sheet.getRange("L3");
  var cellValue = cell.getValue();
  cell.setValue(cellValue + 1);
};

It submits the Customer PO and Packing slip to another sheet as a KEY table.

I then have to create a look-up table to reference to the psKey (packing slip key table) to return matching relationship to what has been shipped on a customer's Purchase Order # and what is on back-order.

It would have been nice to find a way to write directly to the Order Table matching Packing Slip ID to the Customer Purchase Order ID but I fail to find a way of doing that using google app script, as it is over my head.

Google Sheets App Script - Menu Item Link Purchase Order # to Packing Slip #

1 Answers