Google Form to Clean Data Table with Apps Script for online ads and ROAS

Project info

• ThemeOnline Ads and ROAS Tracker
• FeatureAutomation · Advanced
• ToolGoogle Sheets
• Price (one price)US$38

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:

• Ad Campaign
• Category
• Run Days
• Daily Spend
• Status
• Start Date

Master sheet items

Dashboard: Ads and ROAS Dashboard

• Total Ad Spend
• Total Ad Revenue
• Average ROAS
• Losing Campaigns

Tips

• Log spend and revenue per campaign per day
• Pause ads with ROAS below target
• Scale ads with consistently high ROAS

The sample data is synthetic.

Price and ordering

Frequently asked questions