Google Sheets as an Admin Dashboard

Create a new Google Sheet with two tabs:

• • "Data" - Your raw data (sales, leads, metrics, etc.)
• • "Dashboard" - Summary view with charts

In the Data tab, set up columns for: Date, Category, Amount, Status

In your Dashboard tab, add these key metrics:

=SUMIF(Data!D:D,"Complete",Data!C:C)  // Total completed
=COUNTIF(Data!D:D,"Pending")           // Pending count
=AVERAGEIF(Data!B:B,"Sales",Data!C:C)  // Avg sales value
=SPARKLINE(Data!C2:C30)                // Mini chart

1. Select your metric cells

2. Go to Format → Conditional formatting

3. Set rules like:

• • Green if value > target
• • Yellow if within 10% of target
• • Red if below 90% of target

1. Select your data range

2. Insert → Chart

3. Recommended charts:

• • Line chart for trends over time
• • Pie chart for category breakdown
• • Bar chart for comparisons

4. Move charts to your Dashboard tab

Go to Extensions → Apps Script and paste:

function sendWeeklyReport() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var dashboard = sheet.getSheetByName("Dashboard");

  // Get key metrics
  var totalSales = dashboard.getRange("B2").getValue();
  var pendingCount = dashboard.getRange("B3").getValue();

  // Create email
  var subject = "Weekly Dashboard Report - " + new Date().toDateString();
  var body = "Weekly Summary:\\n\\n" +
             "Total Sales: $" + totalSales + "\\n" +
             "Pending Items: " + pendingCount + "\\n\\n" +
             "View full dashboard: " + sheet.getUrl();

  // Send email
  MailApp.sendEmail("your@email.com", subject, body);
}

// Set up weekly trigger
function createTrigger() {
  ScriptApp.newTrigger("sendWeeklyReport")
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(9)
    .create();
}

Run createTrigger() once to schedule weekly emails.

Pull data from external sources automatically:

// Import from another spreadsheet
=IMPORTRANGE("spreadsheet_url","Sheet1!A:D")

// Import from CSV URL
=IMPORTDATA("https://example.com/data.csv")

// Import from API (with Apps Script)
function fetchAPIData() {
  var response = UrlFetchApp.fetch("https://api.example.com/data");
  var data = JSON.parse(response.getContentText());
  // Write to sheet...
}