wholesale_daily_copy_script

Daily Data Copy Script for Wholesale Customers Report

Overview

This script copies data from Wholesale Customers Data - From Snowflake to Report - All Wholesale Customers once per day, allowing normal filtering and sorting without formula conflicts.

---

Google Apps Script Code

Step 1: Open Apps Script Editor

1. In your Google Sheet, go to Extensions → Apps Script
2. Delete any existing code
3. Paste the code below

Step 2: Paste This Script

/**
 * Daily copy of wholesale customer data from Snowflake sheet to Report sheet
 * Runs automatically once per day at 6 AM
 */
 
function copyWholesaleData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Source and destination sheet names
  const sourceSheetName = 'Wholesale Customers Data - From Snowflake';
  const destSheetName = 'Report - All Wholesale Customers';
  
  try {
    // Get sheets
    const sourceSheet = ss.getSheetByName(sourceSheetName);
    const destSheet = ss.getSheetByName(destSheetName);
    
    if (!sourceSheet) {
      Logger.log('Error: Source sheet "' + sourceSheetName + '" not found');
      return;
    }
    
    if (!destSheet) {
      Logger.log('Error: Destination sheet "' + destSheetName + '" not found');
      return;
    }
    
    // Get all data from source (starting from row 2, excluding headers)
    const sourceData = sourceSheet.getRange(2, 1, sourceSheet.getLastRow() - 1, sourceSheet.getLastColumn()).getValues();
    
    // Clear existing data in destination (starting from row 2, keep headers)
    const destLastRow = destSheet.getLastRow();
    if (destLastRow > 1) {
      destSheet.getRange(2, 1, destLastRow - 1, destSheet.getLastColumn()).clearContent();
    }
    
    // Write new data to destination (starting from row 2)
    if (sourceData.length > 0) {
      destSheet.getRange(2, 1, sourceData.length, sourceData[0].length).setValues(sourceData);
      
      Logger.log('Successfully copied ' + sourceData.length + ' rows from ' + sourceSheetName + ' to ' + destSheetName);
    } else {
      Logger.log('Warning: No data to copy from source sheet');
    }
    
  } catch (error) {
    Logger.log('Error copying data: ' + error.toString());
    throw error;
  }
}
 
/**
 * Manual trigger function - run this to test or copy data immediately
 */
function manualCopy() {
  copyWholesaleData();
  SpreadsheetApp.getUi().alert('Data copy completed!');
}
 
/**
 * Set up daily trigger (run this once to schedule daily updates)
 */
function setupDailyTrigger() {
  // Delete existing triggers for this function
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === 'copyWholesaleData') {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  // Create new daily trigger at 6 AM
  ScriptApp.newTrigger('copyWholesaleData')
    .timeBased()
    .everyDays(1)
    .atHour(6)
    .create();
  
  Logger.log('Daily trigger set up successfully - will run at 6 AM every day');
  SpreadsheetApp.getUi().alert('Daily trigger set up! Data will copy automatically at 6 AM.');
}

---

Setup Instructions

Step 1: Add the Script

1. Open your Google Sheet
2. Extensions → Apps Script
3. Paste the code above
4. Click Save (Ctrl+S / Cmd+S)
5. Name the project: “Wholesale Data Copy”

Step 2: Set Up Daily Trigger (One-Time Setup)

1. In Apps Script editor, select the function setupDailyTrigger from the dropdown
2. Click Run (▶️)
3. Authorize permissions when prompted:
   • Click “Review permissions”
   • Choose your Google account
   • Click “Advanced” → “Go to [Project Name] (unsafe)”
   • Click “Allow”
4. You’ll see an alert: “Daily trigger set up! Data will copy automatically at 6 AM.”

Step 3: Test the Script (Optional)

1. Select the function manualCopy from the dropdown
2. Click Run (▶️)
3. Check your “Report - All Wholesale Customers” sheet
4. Data should be copied (excluding headers)

---

How It Works

1. Source: Wholesale Customers Data - From Snowflake

   • Reads data starting from row 2 (skips headers)
   • Gets all columns (A through AP)

2. Destination: Report - All Wholesale Customers

   • Clears existing data (row 2 onwards, keeps headers in row 1)
   • Writes new data starting from row 2

3. Schedule:

   • Runs automatically at 6 AM every day
   • Can also be run manually using manualCopy()

---

Manual Execution

To copy data immediately (without waiting for scheduled run):

Option 1: From Apps Script

1. Extensions → Apps Script
2. Select manualCopy function
3. Click Run (▶️)

Option 2: Add Custom Menu (Optional Enhancement)

Add this to your script to create a menu item:

/**
 * Add custom menu to sheet
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Wholesale Data')
    .addItem('Copy Data Now', 'manualCopy')
    .addToUi();
}

Then you can use: Wholesale Data → Copy Data Now from the menu bar.

---

Customization

Change Schedule Time

In setupDailyTrigger(), modify:

.atHour(6)  // Change 6 to your desired hour (0-23)

Change Schedule Frequency

.everyDays(1)  // Daily
// OR
.everyDays(7)  // Weekly
// OR
.everyHours(12)  // Every 12 hours

Copy Specific Columns Only

Modify the copyWholesaleData() function:

// Instead of all columns, specify range
const sourceData = sourceSheet.getRange(2, 1, sourceSheet.getLastRow() - 1, 20).getValues();
// This copies only first 20 columns

---

Troubleshooting

Script Not Running

1. Check triggers:

   • Extensions → Apps Script
   • Click on clock icon (⏰) in left sidebar
   • Verify trigger exists and is enabled

2. Check logs:

   • Extensions → Apps Script
   • View → Execution transcript
   • Look for errors

3. Test manually:

   • Run manualCopy() function
   • Check if it works

Data Not Copying

1. Verify sheet names match exactly:

   • Check for typos
   • Check for extra spaces

2. Check permissions:

   • Script needs access to read/write sheets
   • Re-authorize if needed

3. Check source data:

   • Ensure source sheet has data
   • Check if source sheet name is correct

Headers Getting Copied

The script is designed to skip headers (starts at row 2). If headers are being copied:

• Check that source sheet has headers in row 1
• Verify the script uses getRange(2, 1, ...) (starts at row 2)

---

Benefits of This Approach

✅ No formula conflicts - Data is static values
✅ Full filtering support - Regular filters work perfectly
✅ Full sorting support - Sort any column without breaking
✅ Automatic updates - Runs daily without manual intervention
✅ Fast performance - No formula recalculation overhead
✅ Reliable - No array formula quirks or conflicts

---

Next Steps

After setting up:

1. ✅ Script is installed
2. ✅ Daily trigger is set (runs at 6 AM)
3. ✅ Headers are in row 1 (manual, static)
4. ✅ Data copies to row 2 onwards (automatic)
5. ✅ Filters and sorting work normally

You can now:

• Add filters: Data → Create a filter
• Sort any column: Click column header → Sort
• Filter by any criteria
• Everything works as expected!

---

Quick Reference

Function	Purpose	When to Use
copyWholesaleData()	Copies data from source to destination	Called automatically by trigger
manualCopy()	Runs copy immediately	Testing or manual refresh
setupDailyTrigger()	Sets up daily schedule	One-time setup