Panduan Lengkap DataInsight

/**
 * Google Apps Script for Google Maps Scraper Integration
 * Version: 1.0.0
 * Last Updated: 2025-01-23
 */

function doPost(e) {
  try {
    Logger.log('Received POST request');
    const data = JSON.parse(e.postData.contents);

    if (!data.orderId || !data.keyword || !data.results) {
      throw new Error('Missing required fields: orderId, keyword, or results');
    }

    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    const sheetName = `Order_${data.orderId.substring(0, 8)}`;
    let sheet = spreadsheet.getSheetByName(sheetName);

    if (!sheet) {
      sheet = spreadsheet.insertSheet(sheetName);
      const headers = [
        'No', 'Nama Bisnis', 'Rating', 'Total Review', 'Kategori',
        'Alamat', 'Telepon', 'Website', 'Jam Buka', 'Latitude',
        'Longitude', 'Google Maps URL', 'Place ID', 'Keyword', 'Timestamp'
      ];

      sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
      sheet.getRange(1, 1, 1, headers.length)
        .setBackground('#667eea')
        .setFontColor('#ffffff')
        .setFontWeight('bold')
        .setHorizontalAlignment('center');
      sheet.setFrozenRows(1);
    }

    const rows = [];
    const startRow = sheet.getLastRow() + 1;
    const timestamp = new Date().toLocaleString('id-ID');

    data.results.forEach((result, index) => {
      rows.push([
        startRow + index - 1,
        result.name || '',
        result.rating || '',
        result.reviewCount || '',
        result.category || '',
        result.address || '',
        result.phone || '',
        result.website || '',
        result.hours || '',
        result.lat || '',
        result.lng || '',
        result.url || '',
        result.placeId || '',
        data.keyword || '',
        timestamp
      ]);
    });

    if (rows.length > 0) {
      const range = sheet.getRange(startRow, 1, rows.length, rows[0].length);
      range.setValues(rows);
      range.setVerticalAlignment('middle');

      for (let i = 0; i < rows.length; i++) {
        if (i % 2 === 0) {
          sheet.getRange(startRow + i, 1, 1, rows[0].length).setBackground('#f7fafc');
        }
      }

      for (let i = 1; i <= rows[0].length; i++) {
        sheet.autoResizeColumn(i);
      }
    }

    createOrUpdateSummary(spreadsheet, data);

    return ContentService.createTextOutput(
      JSON.stringify({
        success: true,
        message: 'Data successfully saved',
        rowsAdded: rows.length
      })
    ).setMimeType(ContentService.MimeType.JSON);

  } catch (error) {
    Logger.log('Error: ' + error.message);
    return ContentService.createTextOutput(
      JSON.stringify({
        success: false,
        error: error.message
      })
    ).setMimeType(ContentService.MimeType.JSON);
  }
}

function createOrUpdateSummary(spreadsheet, data) {
  let summarySheet = spreadsheet.getSheetByName('Summary');

  if (!summarySheet) {
    summarySheet = spreadsheet.insertSheet('Summary', 0);
    const headers = ['Order ID', 'Keyword', 'Total Results', 'Last Updated'];
    summarySheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    summarySheet.getRange(1, 1, 1, headers.length)
      .setBackground('#4c51bf')
      .setFontColor('#ffffff')
      .setFontWeight('bold')
      .setHorizontalAlignment('center');
    summarySheet.setFrozenRows(1);
  }

  const lastRow = summarySheet.getLastRow();
  const existingData = lastRow > 1 ? summarySheet.getRange(2, 1, lastRow - 1, 4).getValues() : [];

  let found = false;
  for (let i = 0; i < existingData.length; i++) {
    if (existingData[i][0] === data.orderId && existingData[i][1] === data.keyword) {
      existingData[i][2] = (parseInt(existingData[i][2]) || 0) + data.results.length;
      existingData[i][3] = new Date().toLocaleString('id-ID');
      summarySheet.getRange(i + 2, 1, 1, 4).setValues([existingData[i]]);
      found = true;
      break;
    }
  }

  if (!found) {
    const newRow = [
      data.orderId,
      data.keyword,
      data.results.length,
      new Date().toLocaleString('id-ID')
    ];
    summarySheet.appendRow(newRow);
  }

  for (let i = 1; i <= 4; i++) {
    summarySheet.autoResizeColumn(i);
  }
}

function doGet(e) {
  return ContentService.createTextOutput(
    JSON.stringify({
      status: 'active',
      message: 'GMaps Scraper webhook is ready',
      version: '1.0.0'
    })
  ).setMimeType(ContentService.MimeType.JSON);
}