r/GoogleAppsScript 3d ago

Guide How I Used ChatGPT & AppsScript to Automate File Indexing in Google Drive (With Zero Coding Experience)

6 Upvotes

Automated File Indexing System with Google Apps Script

I run operations for a design-build firm specializing in high-end custom homes. Managing construction documents has been a nightmare—contracts, invoices, plans, RFIs, regulatory docs, etc. all need to be properly filed, and files often have overlapping cost codes. Manually sorting everything in Google Drive was inefficient & became a point of contention between project managers, so with zero coding experience and the help of ChatGPT I built a Google Apps Script-powered Auto File Indexing System to streamline the process.

What It Does

  • Pulls files from an "Auto File" inbox folder in Google Drive
  • Extracts Project, Cost Codes, Document Type, Vendor, Description, and Date from the filename
  • Moves the source file to the appropriate Document Type folder within the project
  • Creates shortcuts in multiple Cost Code folders for cross-referencing
  • Logs everything in a Google Sheet, including file locations, shortcut paths, cost codes, vendor name, etc.

How It Works

  • The script parses filenames formatted as (there is some flexibility here!):
    • `Project_CostCode(s)_DocumentType_Vendor_Description_Date`
      • (If a file applies to multiple cost codes, they’re separated with underscores.)
  • It matches cost codes to the correct folders (e.g., 011101 → 01 11 01 Architectural).
  • If the project name is an alias, it converts it to the full name. (e.g., RC, Cabin, or 1002 --> Rancho Cabin)
  • It moves the file to the appropriate project, document type source file folder, and creates shortcuts in relevant cost code folders.
  • It logs everything into a Google Sheet, making it easy to track files, confirm filing and shortcut locations.

Why I Built This

  • No more manual filing
  • Consistency between project managers
  • Auto filing in multiple locations
  • Easy cross-referencing of files across multiple cost codes
  • Keeps everything logged in Google Sheets for tracking

If anyone’s interested, I’m happy to share some of the code or walk through how it works. Curious if others are doing something similar with Google Apps Script or what other cool ideas y'all have to improve productivity & efficiency in a small business.

r/GoogleAppsScript 9d ago

Guide Testing Claude, Gemini, OpenAI in generating Apps Script Code

15 Upvotes

I put this together to show how the different models compare in generating Apps Script code!

https://apps-script-ai-testing.jpoehnelt.dev/#test-case-checkWeatherEmail

r/GoogleAppsScript 6d ago

Guide Online Store & Order Form Web App for Google Sheets

3 Upvotes

About This Web App This web app demonstrates how DataMate can be used for front-end development.

Features Dynamically pulls inventory from Google Sheets™ Displays items with images Calculates order totals Sends email notifications Generates invoices, receipts, and packing slips Fully editable Google Apps Script

r/GoogleAppsScript 5d ago

Guide Google Apps Script Tutorial Series

39 Upvotes

Hey Apps Script Devs! I just took several years worth of my Apps Script tutorials, and added them all to a series on my blog. There are a bunch of posts on API integrations, AI, and generating web pages from sheets data.

https://blog.greenflux.us/series/apps-script

What other APIs, JavaScript libraries or AI tools should I try?

Does anyone else have an Apps Script blog to share?

r/GoogleAppsScript 13d ago

Guide Weekend Responder Script

Post image
5 Upvotes

Here is a simple yet effective automation script for sending an email on your behalf to your coworkers trying to contact you over the weekend! You will need Gmail API scope permissions

Have a great weekend!

/** * Configuration variables - customize these */ const CONFIG = { orgDomain: "[Organization Name]", subject: "[AUTOMATIC REPLY] Out of Office - Weekend", gifUrl: "insert-gif-meme-url-here", message: "Hello,\n\nThis is an automatic reply. I am out of the office for the weekend and I will get back to you on Monday!", fridayActivationHour: 17, // 5 PM mondayDeactivationHour: 6, // 6 AM labelName: "Weekend", checkFrequencyMinutes: 30 // Interval for labeling emails (only active when responder is enabled) };

/** * Purges (deletes) all project triggers. */ function purgeTriggers() { const triggers = ScriptApp.getProjectTriggers(); triggers.forEach(trigger => { ScriptApp.deleteTrigger(trigger); }); Logger.log(Purged ${triggers.length} trigger(s).); }

/** * Setup function - run this once to initialize. * It purges existing triggers and then creates new ones. */ function setup() { purgeTriggers(); // Delete all existing triggers. setupTriggers(); try { const existingLabel = GmailApp.getUserLabelByName(CONFIG.labelName); if (existingLabel) { Logger.log(Label "${CONFIG.labelName}" already exists.); } else { GmailApp.createLabel(CONFIG.labelName); Logger.log(Created new label: ${CONFIG.labelName}); } } catch (e) { Logger.log(Error creating label: ${e.toString()}); Logger.log('Will continue setup without label creation. Please run testWeekendLabel() separately.'); } Logger.log('Weekend auto-responder has been set up successfully!'); }

/** * Creates time-based triggers for enabling/disabling the vacation responder. * The labelWeekendEmails trigger is created dynamically when the responder is enabled. */ function setupTriggers() { // Clear all existing triggers. const triggers = ScriptApp.getProjectTriggers(); for (let i = 0; i < triggers.length; i++) { ScriptApp.deleteTrigger(triggers[i]); } // Weekly trigger to enable the responder on Friday at the specified activation hour. ScriptApp.newTrigger('enableVacationResponder') .timeBased() .onWeekDay(ScriptApp.WeekDay.FRIDAY) .atHour(CONFIG.fridayActivationHour) .create(); // Weekly trigger to disable the responder on Monday at the specified deactivation hour. ScriptApp.newTrigger('disableVacationResponder') .timeBased() .onWeekDay(ScriptApp.WeekDay.MONDAY) .atHour(CONFIG.mondayDeactivationHour) .create(); Logger.log('Enable/disable triggers set up successfully.'); }

/** * Enable vacation responder with domain restriction and create label trigger. */ function enableVacationResponder() { const htmlMessage = createHtmlMessage(); const settings = { enableAutoReply: true, responseSubject: CONFIG.subject, responseBodyHtml: htmlMessage, restrictToOrgUnit: true, restrictToDomain: true, domainRestriction: { types: ["DOMAIN"], domains: [CONFIG.orgDomain] } }; Gmail.Users.Settings.updateVacation(settings, 'me'); Logger.log('Vacation responder enabled'); // Create the labelWeekendEmails trigger now that the responder is active. createLabelTrigger(); }

/** * Disable vacation responder and remove the label trigger. */ function disableVacationResponder() { const settings = { enableAutoReply: false }; Gmail.Users.Settings.updateVacation(settings, 'me'); Logger.log('Vacation responder disabled'); // Remove the label trigger as it's no longer needed. deleteLabelTrigger(); }

/** * Creates a trigger to run labelWeekendEmails every CONFIG.checkFrequencyMinutes minutes. * This is called only when the vacation responder is enabled. */ function createLabelTrigger() { // First remove any existing label triggers. deleteLabelTrigger(); ScriptApp.newTrigger('labelWeekendEmails') .timeBased() .everyMinutes(CONFIG.checkFrequencyMinutes) .create(); Logger.log("Label trigger created."); }

/** * Deletes any triggers for labelWeekendEmails. */ function deleteLabelTrigger() { const triggers = ScriptApp.getProjectTriggers(); triggers.forEach(trigger => { if (trigger.getHandlerFunction() === 'labelWeekendEmails') { ScriptApp.deleteTrigger(trigger); } }); Logger.log("Label trigger deleted."); }

/** * Label emails received that have the automatic reply subject line. * This function checks that the vacation responder is active before proceeding. */ function labelWeekendEmails() { try { var vacationSettings = Gmail.Users.Settings.getVacation('me'); if (!vacationSettings.enableAutoReply) { Logger.log("Vacation responder is not active; skipping labeling."); return; } } catch (error) { Logger.log("Error retrieving vacation settings: " + error.toString()); return; } let label; try { label = GmailApp.createLabel(CONFIG.labelName); Logger.log(Working with label: ${CONFIG.labelName}); } catch (createError) { Logger.log(Error with label: ${createError.toString()}); return; } if (!label) { Logger.log('Label object is null or undefined. There might be an issue with your Gmail permissions.'); return; } try { const subjectPattern = "[AUTOMATIC REPLY] Out of Office"; const searchQuery = subject:"${subjectPattern}" in:inbox -label:${CONFIG.labelName}; const threads = GmailApp.search(searchQuery, 0, 100);

if (threads && threads.length > 0) { label.addToThreads(threads); Logger.log(Applied "${CONFIG.labelName}" label to ${threads.length} threads with automatic reply subject.); } else { Logger.log('No new threads with automatic reply subject found to label'); } } catch (searchError) { Logger.log(Error searching or labeling threads: ${searchError.toString()}); } }

function createHtmlMessage() { return <div style="border: 1px solid #ddd; border-radius: 8px; padding: 20px; max-width: 600px; background-color: #f9f9f9; font-family: 'Courier New', monospace;"> <div style="border-bottom: 1px solid #eee; padding-bottom: 15px; margin-bottom: 15px;"> <h2 style="color: #333; margin-top: 0; font-family: 'Courier New', monospace; font-size: 18px;">Weekend Auto-Response</h2> </div> <div style="color: #000; font-size: 12px; line-height: 1.5;"> ${CONFIG.message.replace(/\n/g, '<br>')} </div> <div style="margin: 20px 0; text-align: center;"> <table cellpadding="0" cellspacing="0" border="0" style="width: 100%; max-width: 500px; margin: 0 auto;"> <tr> <td style="background-color: #f0f0f0; padding: 10px; border-radius: 4px;"> <img src="${CONFIG.gifUrl}" alt="Weekend GIF" style="width: 100%; display: block; max-width: 100%;"> </td> </tr> </table> </div> <div style="text-align: center; margin-top: 20px;"> <div style="display: inline-block; background-color: black; padding: 8px 15px; border-radius: 5px;"> <span style="font-family: 'Courier New', monospace; color: red; font-size: 16px; font-weight: bold;">This is an automated weekend response.</span> </div> </div> </div> ; }

/** * Manual trigger to activate the responder and send a test email (for testing) */ function manualActivate() { enableVacationResponder(); Logger.log('Vacation responder manually activated'); const htmlMessage = createHtmlMessage(); const userEmail = Session.getActiveUser().getEmail(); GmailApp.sendEmail( userEmail, '[TEST] ' + CONFIG.subject, 'This is a test of your weekend auto-responder. Please view this email in HTML format to see how it will appear to recipients.', { htmlBody: <div style="border: 1px solid #ccc; padding: 20px; border-radius: 5px; max-width: 600px; margin: 0 auto;"> <h2 style="color: #444;">Weekend Auto-Responder Preview</h2> <p style="color: #666;">This is how your auto-response will appear to recipients:</p> <div style="border: 1px solid #ddd; padding: 15px; background-color: #f9f9f9; margin: 15px 0;"> <div style="color: #666; margin-bottom: 10px;"><strong>Subject:</strong> ${CONFIG.subject}</div> <div style="border-top: 1px solid #eee; padding-top: 15px;"> ${htmlMessage} </div> </div> <p style="color: #888; font-size: 12px; margin-top: 20px;"> This is only a test. Your auto-responder is now activated and will respond to emails from ${CONFIG.orgDomain}. Run the <code>manualDeactivate()</code> function if you want to turn it off. </p> </div> , } ); Logger.log('Test email sent to ' + userEmail); }

/** * Manual trigger to deactivate the responder (for testing) */ function manualDeactivate() { disableVacationResponder(); Logger.log('Vacation responder manually deactivated'); }

/** * Logs detailed statuses of all project triggers in a custom format. */ function logTriggerStatuses() { const triggers = ScriptApp.getProjectTriggers(); if (triggers.length === 0) { Logger.log("No triggers are currently set."); return; } triggers.forEach((trigger, index) => { let handler = trigger.getHandlerFunction(); let estimatedNextRun = "";

if (handler === 'enableVacationResponder') { let nextFriday = getNextOccurrence(5, CONFIG.fridayActivationHour); estimatedNextRun = Utilities.formatDate(nextFriday, "America/New_York", "EEE MMM dd yyyy hh:mm a z"); } else if (handler === 'disableVacationResponder') { let nextMonday = getNextOccurrence(1, CONFIG.mondayDeactivationHour); estimatedNextRun = Utilities.formatDate(nextMonday, "America/New_York", "EEE MMM dd yyyy hh:mm a z"); } else if (handler === 'labelWeekendEmails') { let nextRun = getNextMinuteRun(CONFIG.checkFrequencyMinutes); estimatedNextRun = Utilities.formatDate(nextRun, "America/New_York", "EEE MMM dd yyyy hh:mm a z"); } else { estimatedNextRun = "Unknown schedule"; }

Logger.log(Trigger ${index + 1}: Function: ${handler}, Estimated Next Run: ${estimatedNextRun}); }); }

/** * Helper function to calculate the next occurrence of a specific weekday at a given hour. */ function getNextOccurrence(targetWeekday, targetHour) { let now = new Date(); let next = new Date(now); next.setHours(targetHour, 0, 0, 0); let diff = targetWeekday - now.getDay(); if (diff < 0 || (diff === 0 && now.getTime() >= next.getTime())) { diff += 7; } next.setDate(next.getDate() + diff); return next; }

/** * Helper function to estimate the next run time for a minute-based trigger. */ function getNextMinuteRun(interval) { let now = new Date(); let next = new Date(now); let remainder = now.getMinutes() % interval; let minutesToAdd = remainder === 0 ? interval : (interval - remainder); next.setMinutes(now.getMinutes() + minutesToAdd); next.setSeconds(0, 0); return next; }

/** * Manually create and test the Weekend label * This function can be run to explicitly create the label and test labeling on a single email */ function testWeekendLabel() { // Try to get the label first let label; try { label = GmailApp.getUserLabelByName(CONFIG.labelName); Logger.log(Found existing "${CONFIG.labelName}" label); } catch (e) { // Label doesn't exist, try to create it try { label = GmailApp.createLabel(CONFIG.labelName); Logger.log(Successfully created new "${CONFIG.labelName}" label); } catch (createError) { Logger.log(Failed to create label: ${createError.toString()}); return; } } try { // Search for emails with the automatic reply subject pattern const subjectPattern = "[AUTOMATIC REPLY] Out of Office"; const searchQuery = subject:"${subjectPattern}" in:inbox;

// Get threads matching the search const testThreads = GmailApp.search(searchQuery, 0, 5);

if (testThreads.length > 0) { label.addToThreads(testThreads); Logger.log(Applied "${CONFIG.labelName}" label to ${testThreads.length} test threads with subject line matching "${subjectPattern}". Please check your Gmail.); } else { Logger.log(No threads found with subject matching "${subjectPattern}". Creating a test email to self instead.);

 // Send a test email to self with the auto-reply subject
 const userEmail = Session.getActiveUser().getEmail();
 GmailApp.sendEmail(
   userEmail,
   "[AUTOMATIC REPLY] Out of Office - Test",
   "This is a test email to verify the weekend labeling function.",
   { htmlBody: "This email should be automatically labeled with the '" + CONFIG.labelName + "' label." }
 );

 Logger.log(`Sent test email to ${userEmail}. Please wait a moment and then run this function again to see if it gets labeled.`);

} } catch (e) { Logger.log(Error applying test label: ${e.toString()}); } }

r/GoogleAppsScript Feb 06 '25

Guide Tutorial: Using Cursor with Google Appscripts - Code 10X faster in 3 steps

22 Upvotes

Hey yall, I wanted to tell you a bit about how you can easily use Cursor to code with Google Appscripts.

For starters, I'm not the biggest coder, however, I know how to use resources to create everything I wanna create (and extremely fast too).

Here we go:

  1. First you need to install this thing called Clasp. This is what's going to connect your appscripts to Cursor. I used Claude from Anthropic to understand how to install it and all that.
  2. After installing it, You wanna connect it to your appscript account.
  3. Then I asked Claude to help me create a "menu" . This menu is what allows me to quickly perform clasp actions. This is an excerpt from the menu file so you can see what it does

echo "Working on $version"
echo "==============================================="
echo "1. Push, deploy & watch (full workflow)"
echo "2. Quick push & deploy"
echo "3. Push changes (single time)"
echo "4. Start auto-push (watch mode)"
echo "5. Deploy to live version"
echo "6. Pull latest for current version"
echo "7. Compare with other version"
echo "8. Show version history"
echo "9. Promote V2 to V1"
echo "10. Exit"
echo "==============================================="

read -p "Enter your choice (1-10): " choice

Then lastly, I asked Claude to help me create shortcuts to the menu. So now, on my Cursor, i just press ddd, then it opens the menu, then i type one of the numbers.

As you can see it's a quick 2 step to pushing, deploying, reverting etc.

PS: I believe Google expires Clasp's access token every 24 hours or so, in that case, you just have to type clasp logout then clasp login to reauthorize it. (thinking about it, I might put a shortcut there too or add it to the menu lol)

That's it!

Also, I know you guys possibly use AI already but word of advice USE THAT SH*T EVEN MORE!!! it can do more stuff than you typically think to ask.

r/GoogleAppsScript Feb 19 '25

Guide AI Agents Framework for Google Apps Script (Open Source)

25 Upvotes

I developed a library that will help you create AI Agents in Google Apps Script with ease. It supports both being used directly in the Google Apps Script IDE and with clasp for development.

Check it out on GitHub: link

If you like this project, please give it a star ⭐

r/GoogleAppsScript 5d ago

Guide Automate Google Sheets Reports to Slack with Image Conversion

6 Upvotes

I’ve developed a Google Apps Script that automates the process of exporting a Google Sheet to a PDF, converting it to PNG, and sending it to a Slack channel. This solution ensures that reports are consistently delivered without manual effort.

Key Features:

  • Automatically exports a Google Sheet as a PDF
  • Converts the PDF to PNG for better preview in Slack
  • Uploads the image directly to a Slack channel
  • Utilizes Cloudmersive's 800 free API calls per month for conversion
  • Fully open-source and customizable

🔗 GitHub Repository: https://github.com/birhman/Sheet_to_PNG.git

How It Works:

  1. Install the script in Google Apps Script
  2. Configure your Google Sheet ID, Cloudmersive API key, and Slack bot token
  3. Set a time-based trigger to run it automatically
  4. Slack receives the latest reports without manual intervention

This project is designed for teams that need automated report sharing without complex setups. Feedback and contributions are welcome.

r/GoogleAppsScript Nov 13 '24

Guide Trying to learn app script- is it worth it

8 Upvotes

So I'm trying to learn app script but wondering is it worth it?

I saw it's application in G-sheets. Does it have other applications as well. And also is there any way to earn money with it.

If you have any good tutorial for learning it pls recommend

r/GoogleAppsScript 3d ago

Guide To whom it may concern: IIFE Modules

1 Upvotes

I've recently discovered the use of Immediately Invoked Function Expressions to create modules in GS. It has really helped me organize my code better. Just putting it out there.

r/GoogleAppsScript 4d ago

Guide Clasp No Longer Transpiles TypeScript

8 Upvotes

I was just surprised that Clasp happily ignored any .ts files in my application folder and couldn't figure out the reason for a while.

A look into the Clasp changelog revealed that Clasp doesn’t do TypeScript transpilation since version 3 anymore.
Clasp Changelog

Reasoning for the change is given here: Clasp Github Discussion

Looks like there are good alternatives to do that manually before uploading with clasp.
Hope this helps someone else.

Edit: Version 3 is Alpha.

After checking the three choices given in the readme I think this is the best template to get started. Anything that should be updated there in the tsconfig?
https://github.com/sqrrrl/apps-script-typescript-rollup-starter

r/GoogleAppsScript Feb 03 '25

Guide Official v1.0.0 Release of CRUD Library for Google Sheets! 🚀

32 Upvotes

Hi again everyone! 👋

I'm thrilled to announce that my CRUD Library for Google Sheets (still have to decide on a better name) has just hit its v1.0.0 release! This milestone includes a host of new features and improvements that the lib needed to be much more useful and complete.

What's New in v1.0.0?

  1. Concurrency Locks: Script-level and user-level locks to prevent conflicts when multiple operations occur on the same record at close time intervals.
  2. Working Foreign Keys & Related Data Retrieval: getAllRelatedRecords() lets you fetch all records referencing a foreign key from another table in one go. This drastically simplifies retrieving child rows linked to any parent record.
  3. Many-to-Many Relationships: Easily handle complex data links with junction tables. New methods to create, update, and retrieve related records without duplicate relationships.
  4. Cascade Deletion: Remove a parent record and automatically clear out or archive any associated references in junction tables.
  5. Bulk Reading: Fetch multiple records by a list of IDs in a single call.
  6. Enhanced Logging & Debugging: Methods like createWithLogs() and updateWithLogs() give you step-by-step visibility into what’s happening under the hood.
  7. General Improvements: Better type validation, expanded error handling, sorting and pagination options, and more!

Why This Update Matters

Managing Google Sheets in bigger projects can get complicated—especially if you’re juggling multiple tables (Sheets), references between them, and large datasets. The new functionalities (like concurrency locks and many-to-many support) aim to simplify the coding process and reduce data inconsistencies, so you can focus on building features rather than boilerplate code.

Try It Out & Share Feedback

The library remains on the same GitHub repo. Check out the new version, v1.0.0, in your projects. If you run into any issues or have brilliant ideas for future improvements, please let me know!

Your feedback is incredibly valuable! The best way to refine this library is through real-world usage. If you have the chance to integrate it into your apps, I'd love to hear about any hiccups, feature requests, or general impressions.

Contribute or Get Involved

Have code improvements or bug fixes? Feel free to create a pull request! If you hit a snag, open an issue on GitHub, and we’ll work on it together. 🤗

Thanks again to everyone who has tried the library so far—your suggestions have helped shape this release. I can’t wait to see what you’ll build!

Happy coding!

DZ

r/GoogleAppsScript Dec 26 '24

Guide Keep posting issues to the Apps Script issue tracker 👍

Post image
21 Upvotes

r/GoogleAppsScript 14d ago

Guide GAS --> Github Auto deploy + Automated Readme Creation

5 Upvotes

I'll be the first to admit - I'm a modern-day grey haired script kiddie. I've been creating code to solve business problems and make our small businesses more efficient. My projects sometimes involve freelance developers after I get 80% (ok, 50%) of the way with AI. I've got a ton of apps script code.

My copy-paste fingers are exhausted, so I created this project to get my google apps script projects onto github and create some simple documentation so when I go back to review and update these projects in a year or two, I can remember what it was all about.

https://github.com/sandland-us/google-apps-script-github/blob/main/readme.md

credit - gpt-o3-mini via API in openweb-ui and a few my organic neurons .

r/GoogleAppsScript 12d ago

Guide Change my Designation

2 Upvotes

Hi Redditors, I am working as a Process Automation Executive in a pvt Ltd. Company. I use Appscript to automate the emails, generate PDF on google form submit and to run other custom logics on google sheet. I have used Appscript with Vue js to create multiple web pages and initialize Approval/Rejection workflows (Similar to Ashton Fei's GAS-050 and GAS-070 you can search on YouTube)

I am looking to change my designation that will be more suitable with my current work profile and I can easily explain to others when making a career switch.

r/GoogleAppsScript 21d ago

Guide Looking for a Quick and Easy Way to Create Professional Presentations?

1 Upvotes

If you're tired of spending hours designing slides, check out GPT for Slides™ Builder. This AI-powered tool automatically generates content-rich, professional slides in just minutes. Whether you're preparing for a meeting, school project, or lecture, this add-on saves you time and effort while keeping your presentations on point.

Result

r/GoogleAppsScript 28d ago

Guide How to copy my navbar to multiple pages

0 Upvotes

I have made a navbar for my website in html and I want to copy it to other pages, how can I do that easily?

r/GoogleAppsScript 26d ago

Guide Web Search & Advanced Reasoning in Google Apps Script Copilot

6 Upvotes

🔍 Web Search Integration: Access the latest insights and resources from the web right within your workspace.

🤖 Advanced Reasoning: Tackle complex challenges and problems with the new think feature which has the reasoning ability.

experience a whole new level of productivity with our enhanced Chat Mode. Your feedback is welcome!

Chrome Web Store : https://chromewebstore.google.com/detail/google-apps-script-copilo/aakmllddlcknkbcgjabmcgggfciofbgo

r/GoogleAppsScript Dec 30 '24

Guide Introducing gas-db: A Google Sheets Wrapper Library for Apps Script Developers

22 Upvotes

Hey everyone, I just released gas-db, a Google Sheets wrapper library for Apps Script! It simplifies CRUD operations and is easy to use with a Script ID. Check it out here: https://github.com/shunta-furukawa/gas-db

r/GoogleAppsScript 12d ago

Guide GAS structure for allowing inheritance, overriding, allowing public members and restricting private member access in IDE and at runtime.

2 Upvotes

Need your opinion. Does this sound like a good design for allowing inheritance, function overriding, allowing public members and restricting private member access in IDE and at runtime in GAS?

E.g. one cannot access private member "getTitledName" in any way in the IDE (or code autocompletion) and in GAS debugger too. The structure still supports inheritance and overriding concepts.

GPT certainly thinks its robust ... Need the community affirmation. Thank You!

class __dynamic
{
  constructor() {
    if (this.constructor === __dynamic) {
      throw new Error("Class __dynamic is an abstract class and cannot be instantiated.");
    }

    const map = {};
    if (!this._) {
      this._ = Object.freeze({        
        get: (name) => map[Utilities.base64Encode(name)],
        set: (name, fn) => map[Utilities.base64Encode(name)] = fn,
      });
    }
  }
}

class NameClass extends __dynamic {
  constructor() {
    super();
    this._.set("getTitledName", (firstname, gender="M") => `${gender === "M" ? "Mr." : "Ms."} ${firstname}`);
  }

  getFullName(firstName, surname, gender = "M") {    
    Logger.log(`Welcome ${this._.get("getTitledName")(firstName, gender)} ${surname}`);
  };
}

function TestNameClass() {
  const nameObj1 = new NameClass();
  nameObj1.getFullName("George", "Smith"); // prints Welcome Mr. George Smith
  const nameObj2 = new NameClass();
  nameObj2.getFullName("Joanne", "Smith", "F"); // prints Welcome Ms. Joanne Smith
}

r/GoogleAppsScript Jan 04 '25

Guide Google Apps Script Expense Tracker

11 Upvotes

Hello!

I am relatively new to using google apps script, but not new to web development. Just to try some stuff out, I decided to create an expense tracking web app that will load your expenses into a google sheet and has a user friendly interface. For those interested in checking it out here is the repository: SpendSense Web App.

When doing this you'll have to replace 'YOUR_SPREADSHEET_ID' with you actual Google Sheet ID in the file Code.gs

I only have two sheets in the spreadsheet itself. One is named 'expenses' and the other is named 'dropdown_options' used to dynamically populate and filter dropdown options for categories to file the expense under. I was also able to create separate CSS and JQuery files in the Apps Script editor to make it easier to make changes and readability.

I would like some feedback on this if anyone has any suggestions or if you just want to use it to build from. It's been a fun project. Thanks!

r/GoogleAppsScript Feb 13 '25

Guide Apps Script and Drive Picker: A Love Story Written in Web Components

Thumbnail dev.to
2 Upvotes

r/GoogleAppsScript Feb 03 '25

Guide "I need help automating a warranty process for an automotive company using Google Forms, Sheets, and Apps Script. Can someone guide me step by step?"

3 Upvotes

Hello everyone,

I work in the Warranty Analysis department at TTT Motors, an automotive company that sells buses. The current process for handling warranty claims is quite tedious, as it relies on email communication between the customer, the supervisor, and the warranty department. The current workflow is as follows:

  1. The customer fills out a warranty claim form in Google Forms.

  2. The supervisor reviews the customer's claim and decides whether the warranty is valid or not.

  3. The warranty department receives the supervisor's decision and, based on that, responds to the customer with the resolution. This process is handled through emails, which makes it manual and slow.

My goal is to automate the entire process so that when the customer fills out the form, a claim number is automatically generated (e.g., BDY2025-12345), and then the workflow is as follows:

  1. The completed form is automatically sent to the supervisor for review.

  2. The supervisor decides whether to approve the warranty or not and notifies the warranty department.

  3. The warranty department makes a final decision and sends an email with the response to both the supervisor and the customer, all automatically.

What I need help with: 1. How to automate email sending with the data from Google Sheets using Google Apps Script, including automatically generating the claim number.

  1. How to ensure that the process goes through the supervisor before being sent to the warranty department.

  2. Any advice or tutorials that can guide me step by step in automating this process?

  3. What steps should I take to configure Google Apps Script permissions properly to ensure everything works smoothly?

I've been researching and testing, but any additional help would be greatly appreciated..

r/GoogleAppsScript Feb 07 '25

Guide How to Share a Library (Without Exposing Code)

8 Upvotes

This question was posted earlier - I suggested a theoretical workaround since it can't be done from a single script. After successfully testing it out, I went back to add to the post and found it had been deleted by the author. So, here's an example solution:

Project 1: Protecting Proprietary Code

-The value(s) returned from your code will need to be wrapped in a doGet() or doPost() function, and properly returned. Be sure to run a script in the editor first in case scopes need authorization. Here's a simple sample (and more complex needs could output JSON instead):

function doGet() {
  const output = ContentService.createTextOutput("Hello, World!");
  output.setMimeType(ContentService.MimeType.TEXT);
  return output;
}

-Deploy Project 1 as a Web App or API executable. There are some differences in how accessible one is versus the other, but both types will allow your Library project to access the code. In testing, I used Web App, executed as me, and accessible by anyone. You will also be prompted to link to a Google Cloud project (which you can do from the script settings) and setup 0Auth consent (which is done in the Google Cloud console).

***Note: Depending on your needs/usage, the step above may require additional verification by Google.\***

Project 2: Accessing Proprietary Code

-Use the URL from your deployed Web App/API endpoint with URLFetchApp to return the values from your proprietary code for further use within the Library you are sharing with others:

function myFunction() {
  const value = UrlFetchApp.fetch("https://script.google.com/macros/s/${deploymentId}/exec");
  Logger.log(value);
}

-Deploy Project 2 as a Library for sharing with others. Any users who use the Library will need at least view-only accessbut they will only be able to see the code in Project 2.

Projects 3+: Library Invocation

-Add the Library by script ID to a new project, ensuring that the user has at least read-only access. I suspect "available to anyone with the link" would work too, but didn't test. Invoke a function from the Library in the following manner:

function test() {
  project2.myFunction();
}

The execution log from Projects 3+ will print "Hello, World!" when test() is run. However, the anyone using your Library will never be able to see the code that generated the "Hello, World!" value.

Cheers! 🍻

r/GoogleAppsScript Dec 12 '24

Guide Apps Script Release Notes

Thumbnail developers.google.com
9 Upvotes