Automation · Apps Script

Automation report

A daily work-report sheet where Google Apps Script does the bookkeeping: pick a Video PIC and the row gets that PIC's own code instantly. A daily 18:00 trigger then turns the sheet into a Google Docs report per PIC.

Demo data

Simulation Interactive simulation of my Google Sheets + Apps Script workflow. The real version runs on a private sheet; names and data here are demo values.

Try it: change a Video PIC dropdown.

Daily Work Report · Google SheetsMASTER DATASummary

#	Code ⚡	Video PIC	Date	Script PIC	Script title	Channel	Upload	Progress	Approval

Trigger log

Waiting for an edit…

Daily report

Run the time-based trigger to build the summary per PIC.

Docs report · DocumentApp

Time-driven trigger: daily 18:00 (Asia/Jakarta) → dailyReportJob()

Trigger fireddailyReportJob()
Read MASTER DATArows · PIC · status
Create DocDocumentApp.create()
Report readylink logged

Apps Script · onEdit (simplified)

// Installable trigger: runs when a cell is edited
const PREFIX = { Hakim:'HK', Wafi:'WF', Dewi:'DW', Rafi:'RF' };
function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() !== 'MASTER DATA' || e.range.getColumn() !== COL_PIC) return;
  const pre = PREFIX[e.value]; if (!pre) return;
  const codes = sh.getRange(2, COL_CODE, sh.getLastRow()).getValues().flat();
  const nums = codes.filter(c => String(c).startsWith(pre + '-')).map(c => +String(c).slice(3));
  const next = Math.max(0, ...nums) + 1;  // own counter per PIC
  sh.getRange(e.range.getRow(), COL_CODE).setValue(pre + '-' + String(next).padStart(3, '0'));
}

// Time-driven trigger: daily 18:00 → build the report as a Google Doc
function installDailyTrigger() {
  ScriptApp.newTrigger('dailyReportJob').timeBased().atHour(18).everyDays(1).inTimezone('Asia/Jakarta').create();
}
function dailyReportJob() {
  const rows = SpreadsheetApp.getActive().getSheetByName('MASTER DATA').getDataRange().getValues().slice(1);
  const day = Utilities.formatDate(new Date(), 'Asia/Jakarta', 'yyyy-MM-dd');
  const doc = DocumentApp.create('Daily Work Report ' + day);
  const body = doc.getBody();
  body.appendParagraph('Daily Work Report').setHeading(DocumentApp.ParagraphHeading.TITLE);
  body.appendParagraph('Video production team · ' + day);
  body.appendParagraph('① Per PIC').setHeading(DocumentApp.ParagraphHeading.HEADING2);
  body.appendTable([['PIC', 'Items', 'Codes', 'In review']].concat(summaryByPic_(rows)));
  body.appendParagraph('② Items').setHeading(DocumentApp.ParagraphHeading.HEADING2);
  body.appendTable([['Code', 'PIC', 'Title', 'Progress', 'Approval']].concat(itemRows_(rows)));
  doc.saveAndClose();
  console.log('Report ready: ' + doc.getUrl());
}