如何创建添加和减少按钮以跟踪库存 [英] How to create add and subtract buttons to track inventory

问题描述

我一直在寻找类似的解决方案，但到目前为止还很短.这是我要完成的事情:

I've been searching for similar solutions out there but am coming up short so far. Here is what I want to accomplish:

我需要提出一个基本的解决方案，以便在每天结束时同步库存数量.我们会对一天中售出的库存进行实际盘点，但需要一些记录这些更改并在用户之间共享的东西.我想利用两个按钮(单击一个按钮减去一天结束时售出的物品数量，然后单击一个按钮添加新收到的库存).

I need to come up with a basic solution to sync inventory quantities at the end of each day. We take physical counts of inventory sold throughout the day but need something to log these changes and share between users. I would like to utilize two buttons (click one to subtract amount of items sold at the end of the day and click one button to add newly received inventory).

这是我的工作表的设置方式:

This is how my sheet is set up:

颜色A:产品标签
B栏:产品sku
C栏:今天售出的数量
D栏:总库存数量
E栏:添加新库存

Col A: Product Tag
Col B: Product sku
Col C: Amount Sold Today
Col D: Total Inventory Quantity
Col E: Add New Inventory

D列将预先填充初始库存计数.在每天结束时，我想查看我的产品列表，并在当天在C列中填写每笔售出的商品的数量.C列完全填充后，我想单击减"按钮并输入从D列减去C列.

Column D will be pre-populated with initial inventory counts. At the end of each day, I would like to go down my product list and fill in the amount of each item sold that day in Column C. Once Column C is fully populated, I would like to click the "subtract" button and have Column C subtracted from Column D.

另一方面，一旦我们收到某物品的新库存，我想将这些计数输入到E列中.一旦此列被完全填充，我想单击添加"按钮并将E列添加到D列.理想情况下，一旦完成加法或减法功能，C列或E列将被清除并为第二天的输入做好准备.

On the other side, once we receive new stock of an item I would like to enter these counts into Column E. Once this column is fully populated, I would like to click the "Add" button and have Column E added to Column D. Ideally once the add or subtract function has been completed, columns C or E will be cleared and ready for the next days entry.

我已经设计了按钮，我只需要帮助脚本即可完成此操作.

I already have designed my buttons, I just need help coming up with the scripts to accomplish this.

推荐答案

您可以为此使用Google Apps脚本.

You can use Google Apps Script for this.

如果您不熟悉，请在特定的电子表格中转到工具→脚本编辑器，然后选择空白项目"选项.

If you are unfamiliar, in your particular spreadsheet, go to Tools → Script Editor and then select the Blank Project option.

然后，您可以编写类似的功能来实现所需的功能！

Then you can write functions like this to achieve what you want!

function subtractSold() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var c1 = sheet.getRange("C2");
  var c2 = sheet.getRange("D2");

  while (!c1.isBlank() && !c2.isBlank()){
    c2.setValue(c2.getValue() - c1.getValue());
    c1.clear();
    c1 = c1.offset(1, 0);
    c2 = c2.offset(1, 0);
  }
}

该函数的主要作用是:

1. 获取对活动电子表格的引用
2. 获取第一行数据对单元格C2和D2的引用.
3. 使用while循环重复遍历所有行.当任一单元为空时终止.
4. 在循环中，我们获得适当的值，减去后将其设置回单元格.然后清除C列中的单元格.然后将两个单元格引用向下移动一行(offset方法将返回对原始单元格的引用，但偏移量为 row，column ).

1. Get a reference to the active spreadsheet
2. Get references to the cells C2 and D2, for the first row of data.
3. Use a while loop to repeated go through the rows. Terminate when either cell is empty.
4. In the loop, we get the appropriate values, subtract and set the value back into the cell. Then we clear the cell in column C. We then move both cell references down by one row (the offset method returns a reference to the original cell, but offset by row, column).

然后通过在按钮的分配脚本"选项中输入功能名称(在这种情况下为SubtractSold)来将脚本分配给按钮图像.

Then assign the script to the button image by entering the name of the function (subtractSold in this case) in the "Assign script" option for the button.

我在这里做了一个示例表(转到文件&rarr；进行复制以尝试脚本并查看代码):

I have made an example sheet here (go to File → Make a Copy to try the scripts and see the code): https://docs.google.com/spreadsheets/d/1qIJdTvG0d7ttWAUEov23HY5aLhq5wgv9Tdzk531yhfU/edit?usp=sharing

快一点

如果您尝试上面的工作表，您会看到它一次处理一行，当您有很多行时，这可能会变得很慢.批量处理整个列可能会更快，但了解起来可能会有些复杂:

If you try the sheet above you can see it processes one row at a time, which might get pretty slow when you have a lot of rows. It is probably faster to process the entire column in bulk, but it may be a bit more complicated to understand:

function subtractSoldBulk() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var maxRows = sheet.getMaxRows();

  var soldRange = sheet.getRange(2, 3, maxRows); // row, column, number of rows
  var totalRange = sheet.getRange(2, 4, maxRows);

  var soldValues = soldRange.getValues();
  var totalValues = totalRange.getValues();
  for (var row in soldValues) {
    var soldCellData = soldValues[row][0];
    var totalCellData = totalValues[row][0];

    if (soldCellData != "" && totalCellData != "") {
      totalValues[row][0] = totalCellData - soldCellData;
      soldValues[row][0] = "";
    }
  }

  soldRange.setValues(soldValues);
  totalRange.setValues(totalValues);
}

此处的区别在于，我们得到的不是一个单元格，而是一个范围的单元格.然后，getValues()方法为我们提供了该范围内的二维数据数组.我们对两个数组进行计算，更新数组中的数据，然后根据数组数据设置范围的值.

The difference here is that instead of getting one cell, we get one range of cells. The getValues() method then gives us a 2D array of the data in that range. We do the calculations on the two arrays, update the data in the arrays, and then set the values of the ranges based on the array data.

您可以从Google文档中找到上述方法的文档: https://developers.google.com/apps-script/reference/spreadsheet/sheet

You can find documentation for the methods used above from Google's documentation: https://developers.google.com/apps-script/reference/spreadsheet/sheet

这篇关于如何创建添加和减少按钮以跟踪库存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！