Google Sheets Toggle Checkbox

2025-04-09 14:45 UTC gpt-4o Open in ChatGPT ↗

You

format the cell containing the “pending” value to be a Toggle (google sheets)

function doPost(e) {

var apiKey = e.parameter.apiKey;

// Verify the API key matches your expected key. if (apiKey !== “2oCuCDvyW1Yh9Gd3jWdqNj6HiORNS7J0Vv9ggvMbYZVdAmYSiT8whoqlf9cV7hul”) { return ContentService.createTextOutput(“Unauthorized”); }

try {

// Open the active spreadsheet
var sheet = SpreadsheetApp.openById("1yasCyBQuKt00miqRAaP3uYAd0IoV_ll8MPx7n901cb8").getActiveSheet();
// var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

// Parse the incoming POST request
var data = JSON.parse(e.postData.contents);

var org = data.organization;
var email = data.email;
var timestamp = new Date(data.time);
var pending = data.pend;

// Append the data
sheet.appendRow([org, email, timestamp, pending]);

// Format the date column
// formatDateColumn(sheet, "timestamp");

//return ContentService.createTextOutput(
//  JSON.stringify({ status: 'success' })
//).setMimeType(ContentService.MimeType.JSON);
return ContentService.createTextOutput("Data successfully written to the sheet.");

} catch (error) { // return ContentService.createTextOutput( // JSON.stringify({ status: ‘error’, message: error.message }) // ).setMimeType(ContentService.MimeType.JSON); return ContentService.createTextOutput(error.message); } }

ChatGPT

Alright!
What you’re asking is: after you append the row, you want the “Pending” cell in the newly added row to become a Toggle button in Google Sheets.

In Google Sheets, toggles are basically checkboxes, because there isn’t a native “toggle” UI. You can do this by setting the data validation rule of the cell to be a checkbox.

Let’s update your script!
I’ll show you exactly how to do it:

Here’s the improved code with the checkbox (toggle) logic added:

function doPost(e) {
  var apiKey = e.parameter.apiKey;

  // Verify the API key matches your expected key.
  if (apiKey !== "2oCuCDvyW1Yh9Gd3jWdqNj6HiORNS7J0Vv9ggvMbYZVdAmYSiT8whoqlf9cV7hul") {
    return ContentService.createTextOutput("Unauthorized");
  }

  try {
    // Open the active spreadsheet
    var sheet = SpreadsheetApp.openById("1yasCyBQuKt00miqRAaP3uYAd0IoV_ll8MPx7n901cb8").getActiveSheet();

    // Parse the incoming POST request
    var data = JSON.parse(e.postData.contents);

    var org = data.organization;
    var email = data.email;
    var timestamp = new Date(data.time);
    var pending = data.pend;

    // Append the data
    var lastRow = sheet.appendRow([org, email, timestamp, pending]).getLastRow();

    // Set the pending cell (column D) as a checkbox
    var pendingCell = sheet.getRange(lastRow, 4); // 4th column is 'Pending'
    pendingCell.insertCheckboxes();

    return ContentService.createTextOutput("Data successfully written to the sheet.");

  } catch (error) {
    return ContentService.createTextOutput(error.message);
  }
}

Explanation:

• After appending the row, we get the lastRow.
• We select the “pending” cell in that row with getRange(lastRow, 4) (since it’s the 4th column).
• Then, we apply insertCheckboxes() to turn it into a toggle/checkbox ✅

Result:

Now every time you receive a new POST request, the Pending cell will automatically become a checkbox toggle!

---

Optional improvement:

If you also want to set the checkbox to checked/unchecked depending on the value of pending, let me know! I can show you how to pre-fill it too 😎

Would you like me to add that as well?

You

yes. I actually receive “True” and “False” values. is this enough?

ChatGPT

Perfect!
If you’re already receiving "True" and "False" (as strings), you’re 95% there.

We just need to:

• Convert that string to a boolean.
• After inserting the checkbox, set its value accordingly.

Here’s the final improved version of your code:

function doPost(e) {
  var apiKey = e.parameter.apiKey;

  // Verify the API key matches your expected key.
  if (apiKey !== "2oCuCDvyW1Yh9Gd3jWdqNj6HiORNS7J0Vv9ggvMbYZVdAmYSiT8whoqlf9cV7hul") {
    return ContentService.createTextOutput("Unauthorized");
  }

  try {
    // Open the active spreadsheet
    var sheet = SpreadsheetApp.openById("1yasCyBQuKt00miqRAaP3uYAd0IoV_ll8MPx7n901cb8").getActiveSheet();

    // Parse the incoming POST request
    var data = JSON.parse(e.postData.contents);

    var org = data.organization;
    var email = data.email;
    var timestamp = new Date(data.time);
    var pending = data.pend;

    // Append the data
    sheet.appendRow([org, email, timestamp, pending]);
    var lastRow = sheet.getLastRow();

    // Set the pending cell (column D) as a checkbox
    var pendingCell = sheet.getRange(lastRow, 4); // 4th column is 'Pending'
    pendingCell.insertCheckboxes();

    // Set the checkbox value based on 'pending'
    var isChecked = (pending === "True"); // Convert string to boolean
    pendingCell.setValue(isChecked);

    return ContentService.createTextOutput("Data successfully written to the sheet.");

  } catch (error) {
    return ContentService.createTextOutput(error.message);
  }
}

What changed:

✅ pendingCell.insertCheckboxes(); — insert the checkbox
✅ var isChecked = (pending === "True"); — convert string to boolean
✅ pendingCell.setValue(isChecked); — set the initial value of the checkbox!

Result:

• If your API sends "True" — the checkbox is checked
• If it sends "False" — the checkbox is unchecked

---

Optional:

If you want, I can also show you how to:

• Make the column always have checkboxes by default (even future rows).
• Add conditional formatting (like green for checked, red for unchecked).
• Or even trigger actions when the checkbox is toggled.

Would you like me to show you any of these? 🚀

You

I now want to set a conditional formatting for the entire sheet. if the pending is TRUE i want the entire row to be red. otherwise green

ChatGPT

Awesome — this is a great upgrade! 🔥
You want conditional formatting for the entire row, based on the value of the “Pending” column (which is column D in your case).

Here’s how to do it in Apps Script!

Add this to your function (I’ll show you where in a sec).

function setConditionalFormatting(sheet) {
  // First, clear any existing rules to avoid duplicates
  sheet.clearConditionalFormatRules();

  // Get the range you want to apply formatting to (all data rows)
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()); // Start from row 2 to skip headers

  var rules = [];

  // Rule 1: If Pending is TRUE, set the row to red
  var ruleRed = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=$D2=TRUE`)
    .setBackground('#FFCCCC') // Light red
    .setRanges([range])
    .build();
  rules.push(ruleRed);

  // Rule 2: If Pending is FALSE, set the row to green
  var ruleGreen = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=$D2=FALSE`)
    .setBackground('#CCFFCC') // Light green
    .setRanges([range])
    .build();
  rules.push(ruleGreen);

  // Apply rules to the sheet
  sheet.setConditionalFormatRules(rules);
}

Now, call this function at the end of your doPost:

Just before return ContentService.createTextOutput("Data successfully written to the sheet.");, add:

setConditionalFormatting(sheet);

Final full script:

Here’s your full script with everything neatly together:

function doPost(e) {
  var apiKey = e.parameter.apiKey;

  if (apiKey !== "2oCuCDvyW1Yh9Gd3jWdqNj6HiORNS7J0Vv9ggvMbYZVdAmYSiT8whoqlf9cV7hul") {
    return ContentService.createTextOutput("Unauthorized");
  }

  try {
    var sheet = SpreadsheetApp.openById("1yasCyBQuKt00miqRAaP3uYAd0IoV_ll8MPx7n901cb8").getActiveSheet();

    var data = JSON.parse(e.postData.contents);

    var org = data.organization;
    var email = data.email;
    var timestamp = new Date(data.time);
    var pending = data.pend;

    sheet.appendRow([org, email, timestamp, pending]);
    var lastRow = sheet.getLastRow();

    var pendingCell = sheet.getRange(lastRow, 4); // Column D
    pendingCell.insertCheckboxes();

    var isChecked = (pending === "True");
    pendingCell.setValue(isChecked);

    // Apply conditional formatting
    setConditionalFormatting(sheet);

    return ContentService.createTextOutput("Data successfully written to the sheet.");

  } catch (error) {
    return ContentService.createTextOutput(error.message);
  }
}

function setConditionalFormatting(sheet) {
  sheet.clearConditionalFormatRules();

  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return; // Avoid errors if there are no data rows

  var range = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());

  var rules = [];

  var ruleRed = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=$D2=TRUE`)
    .setBackground('#FFCCCC')
    .setRanges([range])
    .build();
  rules.push(ruleRed);

  var ruleGreen = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=$D2=FALSE`)
    .setBackground('#CCFFCC')
    .setRanges([range])
    .build();
  rules.push(ruleGreen);

  sheet.setConditionalFormatRules(rules);
}

Result:

✅ Pending = TRUE → full row is red
✅ Pending = FALSE → full row is green

---

Bonus Tip:

If you also want to apply this formatting to future rows automatically (not just existing ones), I can show you how to apply it to full columns instead of fixed ranges. It’ll auto-expand 🚀

Would you like me to give you that final “dynamic formatting” trick too? ✨

You

yes. But i’d rather set it up on the sheet, and not via code

ChatGPT

Ahhh — I love that.
Yes, if you want dynamic formatting and prefer to set it up directly in the Google Sheet UI (rather than code), that’s actually cleaner for ongoing use.

Here’s exactly how to do it manually in the sheet — and it will work for all future rows, without touching code.

---

Steps to set up conditional formatting directly in Google Sheets:

1. Open your Google Sheet.

2. Select the range for the whole sheet:

   • Click the first cell of your data (e.g., A2) — assuming you have headers in row 1.
   • Then drag to select all columns you care about (A, B, C, D…).
   • Or select the entire sheet (click the corner between “A” and “1”).

3. Go to Format > Conditional formatting.

4. In the conditional formatting sidebar:

   • Apply to range:
     Example: A2:D1000 (or even A2:Z if you want it dynamic)

5. Set up the first rule (Pending is TRUE):

   • Format cells if…: Custom formula is
   • Enter this formula:

     =$D2=TRUE

   • Set the formatting style (fill color red).
   • Click Done.

6. Add another rule (Pending is FALSE):

   • Click “Add another rule”.
   • Use this formula:

     =$D2=FALSE

   • Set the formatting style (fill color green).
   • Click Done.

---

Result:

✅ ✅ ✅
Now, every time your script adds a new row and sets the checkbox, the row will automatically turn red (if checked) or green (if unchecked).
No need to maintain this in the script!

---

Optional Tip:

If you want the formatting to apply even beyond current rows, you can apply it to the whole columns like:

A2:Z

Or use full columns:

A:Z

---

Optional Bonus (Pro mode):

If you want to go further:

• Add gradient colors.
• Add icons (✅ ❌).
• Make a rule that highlights unchecked rows as “Action Needed.” I can help you style it nicely!

---

Would you like me to show you how to add icons or improve the visual design? It can look pretty slick 🚦✨

You

How can I make another script that runs periodically (let’s say once every hour), checks which cells are pending, send an email to the person listed on the first column, and then mark them as not pending? what if i want the email sender address to be a specific one (not my own)

ChatGPT

Excellent! You’re thinking automation at scale now.
Let’s go step by step.

Objective:

✅ Check the sheet every hour.
✅ If row is Pending, send an email to the address in column B (you said “first column,” but earlier email was in column B, organization was in A).
✅ After sending, uncheck (mark as not pending).

I’ll give you a complete working script for this — then explain email sender options.

---

Step 1: The Script

Here’s the full function to do this:

function checkPendingAndSendEmails() {
  var sheet = SpreadsheetApp.openById("1yasCyBQuKt00miqRAaP3uYAd0IoV_ll8MPx7n901cb8").getActiveSheet();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();

  for (var i = 1; i < data.length; i++) { // Start from 1 to skip headers
    var row = data[i];
    var org = row[0]; // Column A: Organization
    var email = row[1]; // Column B: Email
    var pending = row[3]; // Column D: Pending (Checkbox)

    if (pending === true) {
      // Send email
      var subject = "Pending Action Required";
      var body = "Hello,\n\nThis is a reminder from " + org + ".\nPlease complete the pending task.\n\nThank you!";

      MailApp.sendEmail(email, subject, body);

      // Uncheck the pending checkbox
      sheet.getRange(i + 1, 4).setValue(false); // i+1 because arrays are zero-indexed but sheet rows start at 1
    }
  }
}

Step 2: Set Up the Trigger

1. Open your Apps Script editor.
2. Go to the clock icon (Triggers) on the left.
3. Click ”+ Add Trigger”.
4. Select checkPendingAndSendEmails function.
5. Choose:
   • Event source: Time-driven
   • Type: Hour timer
   • Frequency: Every hour

Now your function runs hourly! 🎉

---

Step 3: About Sender Email Address

This part is important.

Google Apps Script by default always uses the email address of the account that owns the script.
If you want to send from a specific address, there are some constraints:

✅ Options:

1. Google Workspace (formerly G Suite) account:

   • If your domain admin allows, you can set up “Send As” aliases.
   • Then you can send using:

     MailApp.sendEmail({
       to: email,
       subject: subject,
       body: body,
       from: "custom@yourdomain.com"
     });

   • But this only works if the alias is authorized in Gmail settings.

2. Gmail API (Advanced option):

   • Using Gmail API, you can send as another user if you have the right scopes and domain-wide delegation.
   • Requires Workspace and setup by admin.

3. Third-party email service (like SendGrid, Mailgun, etc.):

   • You can use their APIs to send emails from any address.
   • More control, branding, and tracking.

❌ If you’re using a personal Gmail account:

Google will not let you change the sender. You’ll always send from your own Gmail.

---

Summary:

✅ Script: Done
✅ Trigger: Done
📝 Sender email: Depends on your Google account setup (personal or Workspace). If you want, I can show you how to set up alias sending or even integrate SendGrid / Mailgun.

---

Optional Power-up:

If you want, I can also help you:

• 📝 Log emails sent (so you have records)
• 🕒 Add throttling if you have many rows (avoid quota limits)
• 📊 Create a dashboard showing “Last Notified”
• 📩 Use HTML templates for prettier emails

Would you like me to show you how to make the emails nicer? (HTML styled, include dynamic data, etc.) 🚀

You

what if i’m not using a gmail email address, but a custom one

ChatGPT

Good question — and this is where things get very real.
Let’s unpack this carefully, because you’re asking exactly the right thing.

---

Case: Custom Email Address (but using Google Sheets & Apps Script)

There are two main scenarios:

🟢 Scenario 1: You use Google Workspace, and your custom domain is managed by Google (like hello@yourdomain.com).

✅ If your custom email is hosted on Google Workspace, you can:

• Go into Gmail Settings > Accounts > Send mail as and add/verify your custom address.
• In Apps Script, you can use:

  MailApp.sendEmail({
    to: email,
    subject: subject,
    body: body,
    from: "hello@yourdomain.com"
  });

✅ But! Google will only let you use addresses that have been verified in Gmail settings, and your Workspace admin must allow “Send as” delegation.

🚨 Important: Even with this, the “sender” technically is Google servers. The envelope is Google’s, but the “From” field will be your address. For most users, this is fine.

---

🔴 Scenario 2: You use a fully external email provider (like Outlook 365, Zoho, ProtonMail, etc.)

If your custom email is not connected to Google services, MailApp will not work with your address.

MailApp (and GmailApp) can only send emails from:

• Your Google account email
• Or aliases verified in Google Workspace Gmail settings

External non-Google emails are not supported by MailApp.

---

✅ Solution: Use an External Email Service API (Recommended for your case)

If you’re using an external provider (like Outlook, Zoho, or any SMTP service), you have two good options:

1. Use SMTP Services via Apps Script Libraries (Advanced)

   • There are community libraries that let you send via external SMTP.
   • Example: SMTPApp library.
   • ✅ Sends from any valid SMTP account (like support@yourdomain.com).
   • ❌ Slightly more complex setup (you’ll need SMTP server, port, username, and password).

2. Use an Email API like SendGrid, Mailgun, or Postmark

   • Connect via HTTP requests (fetch).
   • ✅ More professional, reliable, tracking, better deliverability.
   • ✅ Easy to integrate with Apps Script.
   • Example with SendGrid:

     function sendEmailViaSendGrid(email, subject, body) {
       var apiKey = 'YOUR_SENDGRID_API_KEY';
       var payload = {
         personalizations: [{ to: [{ email: email }] }],
         from: { email: "support@yourdomain.com" },
         subject: subject,
         content: [{ type: "text/plain", value: body }]
       };
       var options = {
         method: 'post',
         contentType: 'application/json',
         headers: { Authorization: 'Bearer ' + apiKey },
         payload: JSON.stringify(payload)
       };
       UrlFetchApp.fetch('https://api.sendgrid.com/v3/mail/send', options);
     }

✅ Fully custom sender
✅ No Gmail limits
✅ More scalable

---

Recommendation:

Since you’re using a custom email, I highly recommend SendGrid or Mailgun integration.
You’ll get:

• Professional delivery.
• Control over sender identity.
• No Gmail sending limits.
• Tracking (opens, clicks, etc.)

---

TL;DR:

Case	MailApp Support?	Notes
Custom domain, Google Workspace	✅	Add as alias in Gmail settings.
Custom domain, external provider (Outlook, Zoho, etc.)	❌	Use SMTP or Email API (SendGrid, Mailgun).
Personal Gmail account	❌	Only sends from your Gmail address.

---

Next step:

If you want, I can give you a full working script that uses SendGrid or any other provider you choose — it will work with your custom email no matter where it’s hosted!

👉 Do you want me to build you the full SendGrid (or your provider) version? It will be clean and professional. 🚀

You

ok, I have set up mailgun. show me how to call mailgun api from a google app script

ChatGPT

Perfect — you’re going pro mode now. 😎
Mailgun is an excellent choice for this — good deliverability, easy to integrate.

Let’s go step by step. I’ll give you a ready-to-copy function you can drop into your Apps Script.

Step 1: Get Your Mailgun API Key and Domain

1. Log into Mailgun.
2. Go to Sending > Domain settings.
3. Copy:
   • API key (for Mailgun API — use the private key).
   • Domain (e.g., mg.yourdomain.com).

---

Step 2: Use This Script in Apps Script

Here’s a clean function you can use:

function sendEmailViaMailgun(to, subject, text) {
  var apiKey = "YOUR_API_KEY"; // e.g., key-xxxxxxxxxxxxxxxxxxxx
  var domain = "YOUR_DOMAIN"; // e.g., mg.yourdomain.com
  var senderEmail = "you@yourdomain.com"; // Custom sender email

  var url = "https://api.mailgun.net/v3/" + domain + "/messages";

  var payload = {
    from: senderEmail,
    to: to,
    subject: subject,
    text: text
  };

  var options = {
    method: "post",
    contentType: "application/x-www-form-urlencoded",
    payload: payload,
    headers: {
      "Authorization": "Basic " + Utilities.base64Encode("api:" + apiKey)
    },
    muteHttpExceptions: true
  };

  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response.getContentText());
}

Step 3: Use it in your hourly script!

Now, update your loop like this:

function checkPendingAndSendEmails() {
  var sheet = SpreadsheetApp.openById("1yasCyBQuKt00miqRAaP3uYAd0IoV_ll8MPx7n901cb8").getActiveSheet();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();

  for (var i = 1; i < data.length; i++) { // Skip header
    var row = data[i];
    var org = row[0]; // Column A: Organization
    var email = row[1]; // Column B: Email
    var pending = row[3]; // Column D: Pending

    if (pending === true) {
      var subject = "Pending Action Required";
      var body = "Hello,\n\nThis is a reminder from " + org + ".\nPlease complete the pending task.\n\nThank you!";

      // Send using Mailgun
      sendEmailViaMailgun(email, subject, body);

      // Uncheck the pending checkbox
      sheet.getRange(i + 1, 4).setValue(false);
    }
  }
}

Step 4: Test it!

1. Add a “pending” row with a real email.
2. Run checkPendingAndSendEmails() manually the first time.
3. Check your email inbox 📩

---

Notes:

✅ Works with any Mailgun plan.
✅ No Gmail quota issues.
✅ Fully branded sender!

---

Optional Power-up:

• Add HTML emails (I can show you — Mailgun supports both text and HTML).
• Add logging (track sent emails in a log sheet).
• Add attachments (yep, possible).
• Add reply-to address.
• Add batch sending (if you send to many users at once).

---

Want to level it up?

If you want, I can give you:

1. HTML version of the email for a more professional look.
2. Mailgun “track opens and clicks”.
3. Log email status back into your Google Sheet ✅

Would you like me to add those? It’ll take your automation to the next level 🚀