Google Apps Script: Simpan Data Order ke Spreadsheet

Saya akan membuatkan Google Apps Script API untuk menyimpan data order ini ke Google Spreadsheet.

Google Apps Script Code

// Konfigurasi
const SPREADSHEET_ID = 'GANTI_DENGAN_ID_SPREADSHEET_ANDA';
const SHEET_NAME = 'Orders';
const API_KEY = 'GANTI_DENGAN_API_KEY_ANDA'; // Opsional untuk keamanan

function doPost(e) {
  try {
    // Validasi API Key (opsional)
    const apiKey = e.parameter.api_key || e.postData?.contents ? JSON.parse(e.postData.contents).api_key : null;
    
    if (API_KEY && apiKey !== API_KEY) {
      return createResponse(401, false, 'Invalid API Key');
    }
    
    // Parse data dari request
    let data;
    if (e.postData && e.postData.contents) {
      data = JSON.parse(e.postData.contents);
    } else {
      data = e.parameter;
    }
    
    // Validasi data required
    if (!data.order_id) {
      return createResponse(400, false, 'Order ID is required');
    }
    
    // Buka spreadsheet
    const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
    let sheet = ss.getSheetByName(SHEET_NAME);
    
    // Buat sheet jika belum ada
    if (!sheet) {
      sheet = ss.insertSheet(SHEET_NAME);
      // Buat header
      sheet.appendRow([
        'Timestamp',
        'Upload Email',
        'Group ID',
        'Order ID',
        'Resi',
        'Kurir',
        'Pengirim Nama',
        'Pengirim Telp',
        'Pengirim Lokasi',
        'Penerima Nama',
        'Penerima Telp',
        'Penerima Alamat',
        'Penerima Kecamatan',
        'Penerima Kabupaten',
        'Penerima Provinsi',
        'Produk Nama',
        'Produk Varian',
        'Produk Qty',
        'Produk Berat',
        'COD',
        'Tanggal Kirim'
      ]);
    }
    
    // Siapkan data untuk disimpan
    const rowData = [
      new Date(), // Timestamp
      data.upload_email || '',
      data.group_id || '',
      data.order_id || '',
      data.resi || '',
      data.kurir || '',
      data.pengirim_nama || '',
      data.pengirim_telp || '',
      data.pengirim_lokasi || '',
      data.penerima_nama || '',
      data.penerima_telp || '',
      data.penerima_alamat || '',
      data.penerima_kecamatan || '',
      data.penerima_kabupaten || '',
      data.penerima_provinsi || '',
      data.produk_nama || '',
      data.produk_varian || '',
      data.produk_qty || '',
      data.produk_berat || '',
      data.cod || '',
      data.tanggal_kirim || ''
    ];
    
    // Simpan ke sheet
    sheet.appendRow(rowData);
    
    return createResponse(200, true, 'Order saved successfully', {
      order_id: data.order_id,
      row: sheet.getLastRow()
    });
    
  } catch (error) {
    return createResponse(500, false, 'Internal server error: ' + error.message);
  }
}

function doGet(e) {
  // Endpoint untuk test atau mendapatkan data
  try {
    const action = e.parameter.action;
    
    if (action === 'test') {
      return createResponse(200, true, 'API is working');
    }
    
    if (action === 'get_orders') {
      const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
      const sheet = ss.getSheetByName(SHEET_NAME);
      
      if (!sheet) {
        return createResponse(404, false, 'Sheet not found');
      }
      
      const data = sheet.getDataRange().getValues();
      const headers = data[0];
      const rows = data.slice(1);
      
      const orders = rows.map(row => {
        let order = {};
        headers.forEach((header, index) => {
          order[header] = row[index];
        });
        return order;
      });
      
      return createResponse(200, true, 'Orders retrieved successfully', {
        total: orders.length,
        orders: orders
      });
    }
    
    return createResponse(400, false, 'Invalid action parameter');
    
  } catch (error) {
    return createResponse(500, false, 'Internal server error: ' + error.message);
  }
}

function createResponse(status, success, message, data = null) {
  const response = {
    status: status,
    success: success,
    message: message,
    timestamp: new Date().toISOString()
  };
  
  if (data) {
    response.data = data;
  }
  
  return ContentService
    .createTextOutput(JSON.stringify(response))
    .setMimeType(ContentService.MimeType.JSON);
}

Cara Setup

Buka Google Apps Script:

Buka script.google.com
Klik "+ New Project"

Paste Code:

Hapus code default
Paste code di atas

Konfigurasi:

Ganti SPREADSHEET_ID dengan ID spreadsheet Anda
Ganti SHEET_NAME jika perlu (default: "Orders")
Ganti API_KEY untuk keamanan (opsional)

Deploy:

Klik "Deploy" → "New deployment"
Pilih type: "Web app"
Execute as: "Me"
Who has access: "Anyone"
Klik "Deploy"
Copy URL yang diberikan

Dokumentasi API

Base URL

https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec

Endpoints

1. POST - Simpan Order Baru

Endpoint: POST /

Headers:

Content-Type: application/json

Request Body:

{
  "api_key": "YOUR_API_KEY",
  "upload_email": "[email protected]",
  "group_id": "-4830105679",
  "order_id": "250929QQ014FUK",
  "resi": "SPXID051798377589",
  "kurir": "SPX",
  "pengirim_nama": "Matrix Helm",
  "pengirim_telp": "6285708308785",
  "pengirim_lokasi": "KAB. KEDIRI, N/A",
  "penerima_nama": "suharmiyati",
  "penerima_telp": "N/A",
  "penerima_alamat": "jl. jatisari III blok G114 rt 06 rw 15, CILENGKRANG, KAB. BANDUNG, JAWA BARAT",
  "penerima_kecamatan": "CILENGKRANG",
  "penerima_kabupaten": "KAB. BANDUNG",
  "penerima_provinsi": "JAWA BARAT",
  "produk_nama": "Kaca Helm Honda Scoopy Lubang Besar",
  "produk_varian": "Clear Lurus+ Rg Baut",
  "produk_qty": "1",
  "produk_berat": "800 gr",
  "cod": "Tidak",
  "tanggal_kirim": "29-09-2025"
}

Response Success (200):

{
  "status": 200,
  "success": true,
  "message": "Order saved successfully",
  "timestamp": "2025-09-29T10:30:00.000Z",
  "data": {
    "order_id": "250929QQ014FUK",
    "row": 2
  }
}

Response Error (400):

{
  "status": 400,
  "success": false,
  "message": "Order ID is required",
  "timestamp": "2025-09-29T10:30:00.000Z"
}

2. GET - Test API

Endpoint: GET /?action=test

Response:

{
  "status": 200,
  "success": true,
  "message": "API is working",
  "timestamp": "2025-09-29T10:30:00.000Z"
}

3. GET - Ambil Semua Orders

Endpoint: GET /?action=get_orders

Response:

{
  "status": 200,
  "success": true,
  "message": "Orders retrieved successfully",
  "timestamp": "2025-09-29T10:30:00.000Z",
  "data": {
    "total": 1,
    "orders": [
      {
        "Timestamp": "2025-09-29T10:30:00.000Z",
        "Upload Email": "[email protected]",
        "Order ID": "250929QQ014FUK",
        ...
      }
    ]
  }
}

Contoh Penggunaan

Menggunakan cURL:

curl -X POST "https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec" \
  -H "Content-Type: application/json" \
  -d '{
    "api_key": "YOUR_API_KEY",
    "upload_email": "[email protected]",
    "order_id": "250929QQ014FUK",
    "resi": "SPXID051798377589",
    "kurir": "SPX",
    "pengirim_nama": "Matrix Helm",
    "penerima_nama": "suharmiyati",
    "produk_nama": "Kaca Helm Honda Scoopy Lubang Besar"
  }'

Menggunakan JavaScript (Fetch):

fetch('https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    api_key: 'YOUR_API_KEY',
    upload_email: '[email protected]',
    order_id: '250929QQ014FUK',
    // ... data lainnya
  })
})
.then(response => response.json())
.then(data => console.log(data));

Fitur Tambahan

✅ Validasi API Key
✅ Auto-create sheet jika belum ada
✅ Timestamp otomatis
✅ Error handling
✅ Response JSON terstruktur
✅ GET endpoint untuk test & retrieve data

Apakah ada yang ingin ditambahkan atau dimodifikasi?

Membuat API Google Apps Script untuk Simpan Data Order ke Spreadsheet