Google Form to Clean Data Table with Apps Script for personal shopping and pre-orders

Project info

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

Functions used

• Apps Script
• onFormSubmit
• Google Form
• namedValues

Form orders turn straight into clean Data rows.

Formulas in the file

How to use it

1. 1Create a Google Form with questions named Item and Qty.
2. 2Link it to this spreadsheet via Responses, Link to Sheets.
3. 3Paste the code in Apps Script.
4. 4Run pasangTriggerForm once and grant permission.
5. 5Submit the form to test and see the new row in Data.

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 onFormSubmit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getSheetByName("Data");
  var master = ss.getSheetByName("Master").getDataRange().getValues();
  var v = e.namedValues;
  var item = String(v["Item"][0]).trim();
  var qty = Number(v["Qty"][0]) || 0;
  var status = data.getRange(2, 6).getValue();
  var harga = 0, kategori = "";
  for (var i = 1; i < master.length; i++) {
    if (String(master[i][0]).toLowerCase() === item.toLowerCase()) {
      item = master[i][0]; harga = master[i][1]; kategori = master[i][2];
    }
  }
  if (harga === 0 || qty <= 0) return;
  data.appendRow([new Date(), item, kategori, qty, harga, status, ""]);
  var r = data.getLastRow();
  data.getRange(r, 7).setFormula("=D" + r + "*E" + r);
}

function pasangTriggerForm() {
  ScriptApp.newTrigger("onFormSubmit")
    .forSpreadsheet(SpreadsheetApp.getActive()).onFormSubmit().create();
}

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