0

I'm trying to make a button on a spreadsheet for an apparel manufacturing business, I have one sheet that we use for stock on hand that then feeds requirements into two other sheets for cutting and sewing based on minimum stock levels, I want to create buttons on the sewing and cutting sheets where I can enter the amount of the item we have cut or sewn click the button and it adds it to stock levels

example, item A is 5 in stock we need 8, so sewing sheet says to make 3 (witch the sheet already does) but when I make that 3 then I want to be able to add the new ones into the stock on hand and minus the ingredients used from that stock on hand from a cell on sewing sheet.

I'm a complete newbie to script I understand the basics of using but barely anything about writing it

any advice would be greatly appreciated

EDIT: sorry super new to this still learning how to ask questions and still only kinda understand what I'm actually asking for, I have scoured the internet for the answer but was only able to find it for excel not google sheets. up until about 3 weeks ago i had never even used a spreadsheet so im kinda learing on the fly.

I have attached some images of the sheets I'm working on witch hopefully provides the information your asking for.

sewing req sheet

so on sheet "sewing req" if I made 5 black on black left faceplates I would put 5 in C2 then hit the "add" button I have put in D2 to run the script witch would do the following,

stock on hand sheet 1

it would add 5 to the existing total in C110 on sheet "stock on hand" (this cell has no equations in it just a number we manually enter during stocktakes) then,

stock on hand 2

it would also minus 5 from cells C78 and cells C66 on sheet "stock on hand" as these ingredients have been used up,

then it would clear the number out of cell C2 on sheet "sewing Req" I already have the script for the clear part.

I understand I would have to create a heap of these macro scripts for each individual button with minor changes to begging and end cell sources as the information jumps around a bit between the pages

EDIT

I have figured out a solution by frankenstiening some script


/** @OnlyCurrentDoc */

function adjust() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("sewing req");
  var targetSheet = ss.getSheetByName("Stock on hand");
  var New = sourceSheet.getRange("C2").getValue();
  var Old = targetSheet.getRange("C10").getValue();
  var ing1old = targetSheet.getRange("C78").getValue();
  var ing2old = targetSheet.getRange("C66").getValue();
  targetSheet.getRange("C10").setValue(New+Old);
  targetSheet.getRange("C78").setValue(ing1old-New);
  targetSheet.getRange("C66").setValue(ing2old-New);


    sourceSheet.getRange('C2').clearContent();
}
9
  • 1
    Welcome to Web Applications. Please add a brief description of your search/research efforts as is suggested in How to Ask. You might find helpful to start by reading developers.google.com/apps-script/guides/sheets. Commented Oct 21, 2020 at 14:15
  • Welcome. You have described your scenario but you have not provided any information about the key data fields on each sheet, or how the sheets relate to/integrate with each other. Would you please edit your question to provide information about sheets data layout and formula and linking between sheets.
    – Tedinoz
    Commented Oct 22, 2020 at 7:18
  • How's this for an idea. Give each product on "sewing" a checkbox. Tick the box when production is complete for an item. Trigger the update script from a menu (or button) to update one or more checkboxes at once. The stock on hand adjustment should be simple; I would hope the ingredients adjustments are also simple, but can't really comment until you explain the data on the various sheets.
    – Tedinoz
    Commented Oct 22, 2020 at 7:18
  • Thanks, Tedinoz, I have updated the information on the question, I'm extremely new to excel but have an obsession with life automation so feel I'm going to love learning a lot about script and macros, I have scoured a ton of the internet for the answer but could only find the script for excel, I cant write script yet but understand how to adjust it. Commented Oct 23, 2020 at 4:12
  • You're right about frankenstein ;). Your spreadsheet is missing a bill-of-materials. Your script may work but there is a better, efficient approach. Every product/part should have a unique ID. In a "Parts" sheet, you need to create a matrix of each part and the quantiy required to make each other part/finished item. For example, "black on black left faceplates" require "x" metres of Black 100% Cotton Brothcloth, and "x" metres of Black Weathproof Canvas. These values can be looked up and the script can be dynamic. I will post a proof-of-concept.
    – Tedinoz
    Commented Nov 10, 2020 at 5:56

1 Answer 1

0

This is a proof-of-concept for a dynamic script based on the OP's scenario.

The "Stock on hand" and "Sewing req" sheets are largely unchanged. However an extra sheet has been added "BOM" (Bill of material) which creates a relationship between every stock item and the parts required to build that item. The output of the answer is simply a listing of an array reflecting the adjustments to be made to stock on hand. AS a proof of concept, there are a couple of important elements not addressed by the script:

  • balances are NOT updated, including (but possibly not limited to)

    • stock on hand balances,
    • "Need to make",
    • "Made today" or
    • "Checkbox".
  • there is only limited error checking

  • how and when balances should be updated is also important. Should updates be held over until the very last lines of code - ensuring that "roll-back" is possible if errors are detected.

These matters are each straightforward, predictable adjustments that can be added to the script.


function proof() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var stksheetname= "Stock on hand";
  var sewsheetname= "sewing req";
  var bomsheetname = "BOM"
  
  var stock = ss.getSheetByName(stksheetname);
  var sewing = ss.getSheetByName(sewsheetname);
  var bom = ss.getSheetByName(bomsheetname);
  
  // get the sewing req data to test for checkbox
  var sewingLR = sewing.getLastRow();
  //Logger.log("DEBUG: the last row = "+sewingLR)
  var sewingrange = sewing.getRange(2,1,sewingLR-1,5); //five columns so that the checkbox column E is included
  var sewingvalues = sewingrange.getValues();

  // get the BOM info
  var bomstartrow=3;
  var bomLR = bom.getLastRow();
  var bomLC = bom.getLastColumn();
  var bomrange = bom.getRange(bomstartrow, 1, bomLR-bomstartrow+1, bomLC);
  //  Logger.log("DEBUG: the bom data is "+bomrange.getA1Notation()); //the bom data is A3:G8
  
  var bomvalues = bomrange.getValues();
  // just get the codes
  var bomcodes= bomvalues.map(function(e){return e[1];});//[[e],[e],[e]]=>[e,e,e]

  // create temporary arrays to hold the data
  var tempbomarray = []
  var tempstkupdate = [];

  // loop through the sewing data
  for (var i = 0; i < sewingLR-1; i++) {

  // test for value of checkbox
    if (sewingvalues[i][4] == true ){
      // Logger.log("DEBUG: i = "+i+", checkbox is TRUE, item name = "+sewingvalues[i][0]+", code = "+sewingvalues[i][1]+", made today = "+sewingvalues[i][3]+",checkbox = "+sewingvalues[i][4]);

      // lookup the code on BOM 
      var result = bomcodes.indexOf(sewingvalues[i][1]);
      // Logger.log("DEBUG: the index result of BOM = "+result);
      // get the whole row for the BOM item
      var bomreq = bomvalues[result]
      // Logger.log("DEBUG: bomvalues = "+bomreq);
      
      //push the details of stock made to the temporeary stock array
      tempstkupdate.push([sewingvalues[i][0],sewingvalues[i][1],sewingvalues[i][3]]);
     
      // loop through the row details starting at colum C (#2)
      for (var b=2;b<bomLC-1;b++){
        // reset the line-by-line array
        var tempbomarray = []

        // test for a BOM quantiy for this item
        if (bomreq[b] >=0 && bomreq[b] !=""){
          // Logger.log("DEBUG: b = "+b+", and qty = "+bomreq[b]+", the part desc = "+bomvalues[0][b]+"the part code = "+bomvalues[1][b])
          // push BOM qunatity onmto the array - note qty multiplied by number made.
          tempbomarray.push(bomvalues[0][b],bomvalues[1][b],bomreq[b]*sewingvalues[i][3]);
          
          // push the row-by-row value to to stock adjustment array
          tempstkupdate.push(tempbomarray);
        }
 
      }
     

    }
    else{
    //Logger.log("DEBUG: checkbox is false");
    
    }
   
  }
   // Logger.log(tempstkupdate);  // display thge stockl update array
     
  return;
}

Sheet sample - Stock on Hand

stockonhand


Sheet sample - Seqing req

sewing

Sheet sample - Bill of Materials

BOM

Sample - Temporary Stock Update array

[[Black on Black Left, 100.0, 5.0], [Black 100% Cotton Broadcloth, 1.0205E7, 5.0], [Black Weathproof Canvas, 1.0305E7, 5.0], [Black on Olive Right, 130.0, 3.0], [Olive 100% Cotton Broadcloth, 1.020545E7, 9.0], [Plum 100% Cotton Broadcloth, 1.0205475E7, 9.0]]


2
  • I mean to add that the design of the "BOM" sheet is not written in stone. This an an area where you you can/adopt difference layouts.
    – Tedinoz
    Commented Nov 10, 2020 at 21:26
  • Oops. Also mean to mention that this script is not a triggered onEdit; in fact, I think this is preferable since you can 1) run it on demand; or 2) wait till the end of the day to run it manually, or iii) create it as a timed "end-of-day" trigger. Again, there are many options some of which will be convenient for the OP working patterns.
    – Tedinoz
    Commented Nov 10, 2020 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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