Apps Script Save-Form Button for personal shopping and pre-orders

Project info

• ThemePersonal Shopper and Pre-order Tracker
• FeatureAutomation · Intermediate
• ToolGoogle Sheets
• Price (one price)US$36

Functions used

• Apps Script
• SpreadsheetApp
• appendRow
• Drawing button

Data entry takes one click with no direct editing of the Data table.

Formulas in the file

How to use it

1. 1Fill the form on the Dashboard: B24 date, B25 item, B26 qty.
2. 2Extensions, Apps Script, paste the simpanDariForm code.
3. 3Insert, Drawing, create a Save button.
4. 4Click the button's three dots, Assign script, type simpanDariForm.
5. 5Grant access on the first click then test saving.

Apps Script code

The script is delivered as a separate Code.gs file inside the ZIP. Paste it into Extensions, Apps Script. A short excerpt:

function simpanDariForm() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var form = ss.getSheetByName("Dashboard");
  var data = ss.getSheetByName("Data");
  var master = ss.getSheetByName("Master").getDataRange().getValues();
  var tanggal = form.getRange("B24").getValue();
  var item = form.getRange("B25").getValue();
  var qty = form.getRange("B26").getValue();
  var harga = 0, kategori = "";
  for (var i = 1; i < master.length; i++) {
    if (master[i][0] == item) { harga = master[i][1]; kategori = master[i][2]; }
  }
  if (!item || qty <= 0 || harga == 0) {
    SpreadsheetApp.getUi().alert("Item atau qty belum benar.");
    return;
  }
  data.appendRow([tanggal, item, kategori, qty, harga, "Baru", ""]);
  var r = data.getLastRow();
  data.getRange(r, 7).setFormula("=D" + r + "*E" + r);
  form.getRange("B25:B26").clearContent();
  SpreadsheetApp.getActive().toast("Tersimpan di baris " + r);
}

Sample content

Columns of the Data sheet:

• Requested Item
• Category
• Quantity Ordered
• Item Price
• Status
• Order Date

Master sheet items

Dashboard: Personal Shopper and Pre-order Dashboard

• Total Orders
• Payments Received
• Estimated Service Profit
• Orders Not Purchased

Tips

• Set a clear pre-order closing date
• Ask for a deposit so orders are not cancelled
• Record exchange rate and shipping to compute profit

The sample data is synthetic.

Price and ordering

Frequently asked questions