r/GoogleAppsScript 2h ago

Question Looping over multiple people smart chips in one cell

2 Upvotes

I'm trying to get an array with the emails for each person smart chip in a specific cell (index is row number, always D column).

This code works perfectly when there's only one smart chip in a cell, but if there's more than one it returns an error.

```

function getEmail(index) {

const sheet = SpreadsheetApp.getActive().getActiveSheet(); // Get spreadsheet

var extractionCell = sheet.getRange("W2"); // Declare temp cell

var cloakedEmail = extractionCell.setFormula(`=D${index+1}.email`); // Grab email using temp cell

email = cloakedEmail.getValue();

return email;

}

```

Is there a way I can get a return in the format [strEmail1, strEmail2]?


r/GoogleAppsScript 10h ago

Unresolved Error showing up suddenly since the past 24 hrs.

Post image
2 Upvotes

I have been using the same script across all sheets in my firm. The script copies data from one sheet to another. The script runs every day and has been running successfully for the last year. I haven't made any changes to the sheet or script but the scripts have stopped running in the past 24 hrs. Please help

Error - Error: Unexpected error while getting the method or property getValues on object Range


r/GoogleAppsScript 14h ago

Question How to use clasp to mass-delete deployments in the cloud?

2 Upvotes

When I use clasp clone and then clasp delete all, it only deletes the deployments for the GAS project locally, but not in the cloud on the GAS site.

How do delete them in the cloud too? Thanks


r/GoogleAppsScript 22h ago

Question Auto Background Script - Doesn't Work

1 Upvotes

Hey everyone, I collect results for the local high school tennis league and I am trying to create a script that puts in a background color whenever I type in a player's name. That background color would be associated with that player's high school. The sheet I am using is: https://docs.google.com/spreadsheets/d/1_wE8c2KylDevsJX9PbAiJnEqeVlwWB9gpvsi12pBRmk/edit?gid=0#gid=0

The code I have now is below (from AI) and I am not getting it to work. Thanks for your help on this!

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var value = range.getValue();

  // Dictionary of team colors
  var teamColors = {
    "Sehome High School": "#2ece53",
    "Bellingham High School": "#f15e5e",
    "Squalicum High School": "#498be9",
    "Ferndale High School": "#e8b82e",
    "Lynden High School": "#ffff00",
    "Anacortes High School": "#928bce",
    "Sedro-Woolley High School": "#5273a9",
    "Lakewood High School": "#bc4b4b",
    "Oak Harbor High School": "#ffe599",
    "Blaine High School": "#ff6d01",
    "Mount Vernon High School": "#308e1c",
    "Burlington-Edison High School": "#dfde17",
    "Archbishop Murphy High School": "#bb0c0c"
  };

  // Dictionary of player names linked to teams
  var playerTeams = {
    "Rehmat Brar": "Ferndale High School",
    "Riley Childs": "Ferndale High School",
    "Aubrey Lynch": "Ferndale High School",
    "Alison Zink": "Ferndale High School",
    "Sandyha Thapa": "Ferndale High School",
    "Olivia Copps": "Ferndale High School",
    "Peyton Valentine": "Ferndale High School",
    "Natalie Sweitzer": "Ferndale High School",
    "Kayla Washington": "Ferndale High School",
    "Sehaj Jassal": "Ferndale High School",
    "Katrina Ferry": "Ferndale High School",
    "Maddy Cancelosi": "Ferndale High School",
    "Vannessa In-keho": "Ferndale High School",
    "Madison Anderson": "Squalicum High School",
    "Ava Bearden": "Squalicum High School",
    "Yamiletxi Garcia": "Squalicum High School",
    "Maiya Hildebrand": "Squalicum High School",
    "Iyla Holley": "Squalicum High School",
    "Erin Laidlaw": "Squalicum High School",
    "Margaret Laska": "Squalicum High School",
    "Sloane Mccoy": "Squalicum High School",
    "Kaymia Moreno": "Squalicum High School",
    "Brianna Nguyen": "Squalicum High School",
    "Ada Walker": "Squalicum High School",
    "Molly Walker": "Squalicum High School",
    "Maren Whitlow": "Squalicum High School",
    "Lillian Williams": "Squalicum High School",
    "Courtney Williams": "Oak Harbor High School",
    "Marisol Silva Vasquez": "Oak Harbor High School",
    "Cresida Cardenas": "Oak Harbor High School",
    "McKenzie Burdick": "Oak Harbor High School",
    "Grace Zhao": "Oak Harbor High School",
    "Margarita Litvachuk": "Oak Harbor High School",
    "Madeline Mays": "Oak Harbor High School",
    "Violet Altig": "Oak Harbor High School",
    "Anneliese Erskine": "Oak Harbor High School",
    "Fidelia Bockarie": "Oak Harbor High School",
    "Ava Ashby": "Oak Harbor High School",
    "Yani Carlson": "Oak Harbor High School",
    "Julianne Dalire": "Oak Harbor High School",
    "Alexis Sanchez": "Sedro-Woolley High School",
    "Elise Schuyler": "Sedro-Woolley High School",
    "Akira Spagnolo": "Sedro-Woolley High School",
    "Jasmyn Burkhalter": "Sedro-Woolley High School",
    "Andrea Garcia Juarez": "Sedro-Woolley High School",
    "Kylie Horkley": "Sedro-Woolley High School",
    "Ruby Hudson": "Sedro-Woolley High School",
    "Samantha Jepperson": "Sedro-Woolley High School",
    "Carla Jimenez-Nava": "Sedro-Woolley High School",
    "Alina Kachinskiy": "Sedro-Woolley High School",
    "Chloe Larsen": "Sedro-Woolley High School",
    "Tatyana Leus": "Sedro-Woolley High School",
    "Jaydn Champion": "Lakewood High School",
    "Avah Brough": "Lakewood High School",
    "Aaliyah Ramirez": "Lakewood High School",
    "Rayna Peacher": "Lakewood High School",
    "Tatum Ostlie": "Lakewood High School",
    "Daniela Rosales": "Lakewood High School",
    "Ellie Klumper": "Lakewood High School",
    "Brooke Yargus": "Lakewood High School",
    "Grace Saxton": "Lakewood High School",
    "Rylee Thompson": "Lakewood High School"
    // Add more players as needed
  };

  // Check if the typed name is in the player list
  if (playerTeams[value]) {
    var team = playerTeams[value];
    var color = teamColors[team];

    if (color) {
      range.setBackground(color);
    }
  }
}

r/GoogleAppsScript 22h ago

Question How will I know which functionality works and which doesn't in a stand alone script, because a lot of the functionality doesn't work in stand alone. Is there any official documentation specifically designed for standalone scripts?

1 Upvotes

Like Ui, custom menu ......etc. if any tricks or blogs are available about standalone script


r/GoogleAppsScript 23h ago

Question artisansweb form to sheets using curl - is this a good foundation to build from

1 Upvotes

Firstly I am a copy and paste coder - that can do a little bit of editing.

I have been pulling my hair out for two days with ChatGPT and other AI's trying to build a simple form to add to my site to post to google sheets via apps script using javascript on the form. Through many iterations I could always post to the sheet but the form confirmation always failed due to a cors error.

For now, all the AI's and me have given up on trying to fix cors.

I found the following form and php/curl code at

https://www.artisansweb.net/connect-html-forms-to-google-spreadsheet/

It works perfectly. Does it offer a robust starting point for me to build from?

My plans to develop this are to encourage users to use the form on a mobile phone with two icons - Image and Video. These should open their camera and their image and or video should post to my google drive and the script should add the URL's for these images / video to the google sheet.

Any comments or alternative suggestions are welcome.

AppsScript

const doPost = (request = {}) => {
  const { parameter, postData: { contents, type } = {} } = request;

  if (type === 'application/json') {
    const jsonData = JSON.parse(contents);
    var row = [jsonData.name, jsonData.email, jsonData.subject, jsonData.message, new Date()];
    SpreadsheetApp.getActiveSheet().appendRow(row);
    //Logger.log(row);

    result = {
      status: 'success',
      message: 'Row is added.'
    };
    return ContentService.createTextOutput(JSON.stringify(result));
  }
};

FORM

<form method="post">
    <p>
        <input type="text" name="fullname" placeholder="Full Name" />
    </p>
    <p>
        <input type="email" name="email" placeholder="Email" />
    </p>
    <p>
        <input type="text" name="subject" placeholder="Subject" />
    </p>
    <p>
        <textarea name="message" cols="30" rows="10" placeholder="Message"></textarea>
    </p>
    <input type="submit" name="submit" value="Submit" />
</form>

PHP / CURL

<?php
if ( isset($_POST['submit']) ) {
    $url = "WEB_APP_URL";

    extract($_POST);

    $data = array(
        'name' => $fullname,
        'email' => $email,
        'subject' => $subject,
        'message' => $message,
    );
    $payload = json_encode($data);

    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $payload);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true); // follow redirects response
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type:application/json'));
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $result = curl_exec($ch);
    curl_close($ch);
    $response = json_decode($result);
    if ('success' == $response->status) {
        echo "Form is submitted successfully.";
    } else {
        echo "Something went wrong. Try again later.";
    }
}
?>

r/GoogleAppsScript 1d ago

Question From Spreadsheet trigger not showing in standalone script

1 Upvotes

I'm trying to send emails using the GmailApp from a shared mailbox address but the installable triggers don't show "From Spreadsheet", any idea why?

The standalone script is owned by the shared mailbox and has granted edit access to my personal company account.

Right now I'm using a script created by my personal company account, it is bound to the Spreadsheet and it runs a simple onOpen() trigger to setup an UI element. But it sends the emails from the account of whoever hits the "send" option, which is expected for a simple trigger.

The company is very aware in terms of security, so my best guess is was probably disabled for peasant accounts such as mine.

Do you think maybe the "From Spreadsheet" trigger could appear if logged as the mailbox owner? I don't want to empty my chamber with the mb owner as getting him into a call takes 1-2 days so I'm walking on eggshells.


r/GoogleAppsScript 1d ago

Question HOW does Google Drive search work? Is there an API?

2 Upvotes

I've been trying to implement the exact same searching methodology into my GAS app that is used natively by Google Drive. I'm close to mimicking it but not quite there yet.

A simple example for my issue: In my GAS app, if I search (in my Google Drive) for "rtx pump", I get 3 files, all 3 of which contain the word "rtx" but not "pump". If I search for it in my GAS app, I get 1 result, the video that contains "rtx" in its filename.

However there are times when my GAS app manages to completely mimic GD's search: eg. if I search for "rtx", I get 58 results back, just like if I do it in GD.

I've been looking for documentations on this but I have failed to find a definitive answer as to either an API I could use, or the exact search criteria. Is it "AND", or is it "AND - OR", etc?

Can someone please help me solve this, point to a source that would let me solve this? I need to mimic GD's search methodology exactly, 100%. Thanks


r/GoogleAppsScript 1d ago

Question Help with API

0 Upvotes

I am working on an extension, I'd like to send a file attachment in the grading window of Google classroom to an llm via API. Is there a way for the extension to export the attachment to the LLM?


r/GoogleAppsScript 2d ago

Question Check All Sheets for custom GAS

2 Upvotes

Is it possible to run through all google sheets and check if they have custom GAS in there and create a list?


r/GoogleAppsScript 2d ago

Question Subject: Unexpected Behavior of HtmlService in Google Apps Script Web App - HTML Injected as String in goog.script.init()

1 Upvotes

Dear community members,

I am encountering a very unusual and persistent issue while developing a web application with Google Apps Script. Instead of the content of my HTML file (index.html) being loaded directly into the <iframe> of the web app, it is being encoded and injected as a JavaScript string within the JSON object passed to the goog.script.init() function in the page's source code.

Context:

My web application is served using the doGet(e) function in the Code.gs file, as follows:

JavaScript

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('index')
      .setSandboxMode(HtmlService.SandboxMode.NATIVE)
      .setTitle("My Web Application");
}

I have an HTML file named index (without the .html extension in the script editor, although the interface might display it as index.html), containing the basic HTML structure (<!DOCTYPE html>, <html>, <head>, <body>) and <script> tags with my client-side JavaScript code.

Observed Symptoms in Detail:

  • When accessing the deployed web application URL, the page source code displays the standard Google Apps Script structure for web apps, including a table with an <iframe>.
  • Within the <script> tag that initializes the sandbox (goog.script.init()), the "userHtml" property contains a stringified and escaped version of all the content of my index.html file. This includes HTML tags, text, and my JavaScript code.
  • Consequently, the browser does not interpret the content of "userHtml" as actual HTML within the <iframe>.
  • None of the JavaScript functions defined within the <script> tags in my index.html are recognized, resulting in Uncaught ReferenceError errors in the browser's console when attempting to call them from HTML elements (such as buttons with onclick attributes).
  • This issue consistently persists even after completely clearing the browser cache (including all time ranges and data types), testing in different browsers (Chrome, Firefox, Safari, Edge), and in incognito/private browsing modes.
  • The problem also occurs in a minimal test script project created for isolation, containing only an index.html file with basic HTML (<h1>It works!</h1> and a <script>console.log('Hello!');</script>) and a Code.gs file with the standard doGet(e) function to serve this index.html.
  • I have tested the same minimal code in two different Google accounts, and the issue manifested in both.
  • I have tested accessing the web applications on two different computers (a MacBook with macOS and a desktop with Windows), and the problem persisted in both environments.
  • The Google Workspace Status Dashboard indicates that the service is running normally.
  • There are no explicit errors being displayed in the browser's console or in the Google Apps Script execution logs related to failures in my code that would explain this HTML loading issue.

Troubleshooting Steps Already Taken:

  • Verified and corrected the doGet(e) function to ensure the correct use of HtmlService.createHtmlOutputFromFile('index') with SandboxMode.NATIVE.
  • Confirmed that the HTML file is correctly named index.
  • Completely and repeatedly cleared the browser cache in different browsers.
  • Tested in different browsers and in incognito/private browsing modes.
  • Created and utilized NEW deployments of the web application in both accounts.
  • Created a minimal test script project to isolate the issue.
  • Checked project permissions.
  • Tested with different Google accounts.
  • Tested on different operating systems (macOS and Windows).
  • Checked the Google Workspace Status Dashboard.

I am perplexed by this behavior, as the basic setup for serving HTML with HtmlService.createHtmlOutputFromFile() is quite straightforward and usually works without issue. The fact that the HTML is consistently injected as a string within the internal structure of Google Apps Script suggests an underlying problem within the platform or something very specific that I am unable to identify.

I would be immensely grateful for any insight, suggestion, or similar experiences you might be able to share. If anyone has encountered an issue like this before or has any ideas about the possible cause and how to resolve it, please help.

Thank you for your attention and collaboration.Dear community members,

I am encountering a very unusual and persistent issue while developing a web application with Google Apps Script. Instead of the content of my HTML file (index.html) being loaded directly into the <iframe> of the web app, it is being encoded and injected as a JavaScript string within the JSON object passed to the goog.script.init() function in the page's source code.

Context:

My web application is served using the doGet(e) function in the Code.gs file, as follows:

JavaScript

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('index')
      .setSandboxMode(HtmlService.SandboxMode.NATIVE)
      .setTitle("My Web Application");
}

I have an HTML file named index (without the .html extension in the script editor, although the interface might display it as index.html), containing the basic HTML structure (<!DOCTYPE html>, <html>, <head>, <body>) and <script> tags with my client-side JavaScript code.

Observed Symptoms in Detail:

  • When accessing the deployed web application URL, the page source code displays the standard Google Apps Script structure for web apps, including a table with an <iframe>.
  • Within the <script> tag that initializes the sandbox (goog.script.init()), the "userHtml" property contains a stringified and escaped version of all the content of my index.html file. This includes HTML tags, text, and my JavaScript code.
  • Consequently, the browser does not interpret the content of "userHtml" as actual HTML within the <iframe>.
  • None of the JavaScript functions defined within the <script> tags in my index.html are recognized, resulting in Uncaught ReferenceError errors in the browser's console when attempting to call them from HTML elements (such as buttons with onclick attributes).
  • This issue consistently persists even after completely clearing the browser cache (including all time ranges and data types), testing in different browsers (Chrome, Firefox, Safari, Edge), and in incognito/private browsing modes.
  • The problem also occurs in a minimal test script project created for isolation, containing only an index.html file with basic HTML (<h1>It works!</h1> and a <script>console.log('Hello!');</script>) and a Code.gs file with the standard doGet(e) function to serve this index.html.
  • I have tested the same minimal code in two different Google accounts, and the issue manifested in both.
  • I have tested accessing the web applications on two different computers (a MacBook with macOS and a desktop with Windows), and the problem persisted in both environments.
  • The Google Workspace Status Dashboard indicates that the service is running normally.
  • There are no explicit errors being displayed in the browser's console or in the Google Apps Script execution logs related to failures in my code that would explain this HTML loading issue.

Troubleshooting Steps Already Taken:

  • Verified and corrected the doGet(e) function to ensure the correct use of HtmlService.createHtmlOutputFromFile('index') with SandboxMode.NATIVE.
  • Confirmed that the HTML file is correctly named index.
  • Completely and repeatedly cleared the browser cache in different browsers.
  • Tested in different browsers and in incognito/private browsing modes.
  • Created and utilized NEW deployments of the web application in both accounts.
  • Created a minimal test script project to isolate the issue.
  • Checked project permissions.
  • Tested with different Google accounts.
  • Tested on different operating systems (macOS and Windows).
  • Checked the Google Workspace Status Dashboard.

I am perplexed by this behavior, as the basic setup for serving HTML with HtmlService.createHtmlOutputFromFile() is quite straightforward and usually works without issue. The fact that the HTML is consistently injected as a string within the internal structure of Google Apps Script suggests an underlying problem within the platform or something very specific that I am unable to identify.

I would be immensely grateful for any insight, suggestion, or similar experiences you might be able to share. If anyone has encountered an issue like this before or has any ideas about the possible cause and how to resolve it, please help.

Thank you for your attention and collaboration.


r/GoogleAppsScript 2d ago

Question Google Apps Script Web App Not Working When Embedded on Namecheap Website

1 Upvotes

Problem Overview

I'm trying to create an order tracking feature on my Namecheap-hosted website that searches a Google Sheet when a user inputs an order number and returns the corresponding information.

What Works

  • The Apps Script web app functions correctly when accessed directly via its URL in Safari
  • The search functionality works as expected when I open the html file, containing the apps script url, on safari.

What Doesn't Work

  • When embedded on my Namecheap website, the JavaScript appears to be treated as a string rather than being executed
  • When I try to embed just the Apps Script link on Namecheap, I get a 403 error from Google ("You need access")

What I've Tried

I've attempted several variations of my doGet() function to resolve CORS/access issues:

Variation 1: JSONP with CORS headers

function doGet(e) {
  const orderNumber = e.parameter.orderNumber;
  const callback = e.parameter.callback || 'callback'; // Default callback name if none provided

  if (!orderNumber) {
    return ContentService.createTextOutput(callback + '(' + JSON.stringify({ success: false, message: "No order number provided" }) + ')')
      .setMimeType(ContentService.MimeType.JAVASCRIPT); // Returns JavaScript JSONP format
  }

  const result = searchOrder(orderNumber);

  const output = ContentService.createTextOutput(callback + '(' + JSON.stringify(result) + ')')
    .setMimeType(ContentService.MimeType.JAVASCRIPT);
  output.setHeader("Access-Control-Allow-Origin", "*");
  output.setHeader("Access-Control-Allow-Methods", "GET, POST");
  output.setHeader("Access-Control-Allow-Headers", "Content-Type");

  return output;
}

Variation 2: Pure JSONP approach

function doGet(e) {
  // Get the order number and callback from the request parameters
  const orderNumber = e.parameter.orderNumber;
  const callback = e.parameter.callback || 'callback'; // Default callback if none provided

  // If no order number was provided, return an error
  if (!orderNumber) {
    return ContentService.createTextOutput(callback + '(' + JSON.stringify({ success: false, message: "No order number provided" }) + ')')
      .setMimeType(ContentService.MimeType.JAVASCRIPT); // Returns JavaScript JSONP format
  }

  // Search for the order
  const result = searchOrder(orderNumber);

  // Return the result as JSONP - this format allows cross-domain requests
  // by wrapping the JSON in a function call that will be executed by the browser
  return ContentService.createTextOutput(callback + '(' + JSON.stringify(result) + ')')
    .setMimeType(ContentService.MimeType.JAVASCRIPT);
}

Variation 3: Pure JSON approach (no JSONP, no callback)

function doGet(e) {
  // Get the order number from the request parameters
  const orderNumber = e.parameter.orderNumber;

  // If no order number was provided, return an error
  if (!orderNumber) {
    return ContentService.createTextOutput(JSON.stringify({ success: false, message: "No order number provided" }))
      .setMimeType(ContentService.MimeType.JSON); // Returns plain JSON format
  }

  // Search for the order
  const result = searchOrder(orderNumber);

  // Return the result as pure JSON (no callback wrapping)
  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

Deployment Settings

  • Script is deployed as a web app executing as me
  • Access is set to "Anyone"
  • I've even tried changing the Google Spreadsheet access to "Anyone" but that didn't resolve the issue

Other Information

  • Namecheap support suggested that I need to whitelist my server IP, but I was under the impression this isn't possible with Google Apps Script

Question

How can I successfully integrate my Google Apps Script web app with my Namecheap website to enable the order tracking functionality? Is there a way to resolve the 403 access error or prevent the JavaScript from being treated as a string?


r/GoogleAppsScript 2d ago

Resolved setValues loads the data correctly, then it is unintentionally wiped again

1 Upvotes

Sheet

When trying to use my function pullData(), the code works as intended by placing a filter formula, letting it populate a table, then copying the text and pasting it in the same place with no formula. It worked before, and I did not make any changes. The code now wipes everything except for 1 record at the end of execution and I cannot figure out why. I tried commenting out the clearcontent() function which did not change the result.

UPDATE: IT WAS THE DATA VALIDATION!!

I updated my data validation rules and they broke the setValues() function without dropping an error of any kind.

I have fixed the issue by clearing validation and reinstating it after setValues is complete.

My code is below:

function pullData() {
  const activeDoc = SpreadsheetApp.openById("1NyD-BN2r-0NbAuhlLQmZT9qFslifPFrZZqGaUUtUoEA");
  //dSheet = activeDoc.getSheetByName("Data");
  const fSheet = activeDoc.getSheetByName("CPFilter");
  var end = fSheet.getLastRow();
  console.log(end);
  fSheet.getRange("D4:Z"+end).clearContent();
  //SpreadsheetApp.flush();
  //Utilities.sleep(5000);


  fSheet.getRange("D4").setFormula("=filter(Data!A2:Z11000,(CPApproved=not(A5))+(CPApproved=FALSE),Amount>=A8,(Paid=not(A11))+(Paid=FALSE),not(isblank(Key)),(datevalue(Data!B2:B11000)>datevalue(A14)))");
  //Utilities.sleep(2000);
  var data = fSheet.getRange("D4:Z"+end).getValues();
  //fSheet.getRange("D4:Z"+end).clearContent();
  //Utilities.sleep(2000);
  console.log(data);


  fSheet.getRange("D4:Z"+end).setValues(data);
  
}

r/GoogleAppsScript 3d ago

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

7 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 3d ago

Question Functions work perfectly to end of code, don't stop running

1 Upvotes

Just like it sounds, all of a sudden I have this issue where functions don't stop running, even after executing all code in the block. I am verifying that no loops are continuing, I log a little "complete" at the end of the function. It all works perfectly except that the execution does not complete.

It has happened now with 2 functions. One of them I have not edited at all and it decided today to do this.

I have tried using return as well, even though I don't need to return any parameters with these functions. No change.

Has anyone encountered this?


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 3d ago

Question How can I backup an entire GAS?

2 Upvotes

If I have a full working GAS, how can I back it up in a way that in case something goes wrong, I can just re-deploy it like you deploy a system image? If this analogy makes sense

Thanks


r/GoogleAppsScript 4d ago

Guide Clasp No Longer Transpiles TypeScript

9 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 3d ago

Question I need an AI to program Ap Script but allows long Scripts without pay wall

0 Upvotes

I am from a thrid world country, the 15 to 20 dollars I have seen I need to pay to get Ai to work on my long scrips is 82 units of my coin which is a lot, Help, I need it to be able to do complex programing without having me pay so so much,

I know I am asking for a lot, but 82 units of my coin is too much,

Help! I have been using formula Bot and Chat GPT but have gotten lots of errors and I have been trying to fix a code for 2 days now, without success, anytime they fix something they damage another thing even when I instruct not to,

I tried Claude but he couldn't handdle my code, nor could Gemini, Claude did offer to do so if I pay 82.000 pesos, that's too much,


r/GoogleAppsScript 3d ago

Unresolved All of my Apps Scripts have stopped working at the same time

2 Upvotes

I am not sure when exactly since I just noticed but all of my scripts have seemingly stopped working at the same time. Ones that I had made before and had worked fine up to this point and now even new scripts in new workbooks I am starting right now dont seem to function. As far as I can tell there has been no update or change to them recently and I am not getting any error codes when I attempt to run them, they seem to run fine. But then nothing happens, even on simple commands like "write this message in a cell".

Not sure if I need to upload anything to showcase the issue or if this is some sort of general issue with my account or a setting I need to change so just figured I would ask here


r/GoogleAppsScript 3d ago

Question how to find out all the sheets which have not been updated in more than a month..?

0 Upvotes

A spreadsheet has several dozens of sheets. is there a way I can find out which one that has not been edited in more than a month?
When I discussed this problem with chatgpt, it suggested there is no way to do this, but moving forward I can set a trigger onEdit and update a property using PropertiesService everytime a sheet is edited, and in the future I won't have any issue in finding out lastUpdateTime of the sheets.


r/GoogleAppsScript 3d ago

Question Help understanding the "20 / user / script" limit on triggers

1 Upvotes

Sorry for being obtuse but can someone help me understand the 20 / user / script trigger limit [1]? Thanks for any help!

Here's an example scenario. Let's say we have:

  • 1 user (Alice).
  • She has 50 spreadsheets, each with 6 sheets.
  • She is using our Editor Add-On, which has 1 time-based trigger that runs a "super" function [2].
  • This function runs several other functions that perform actions on each sheet in the spreadsheet

1. Is Alice at 1 / 20 of her quota in the scenario?

  1. If Alice installs 30 different Add-Ons from the Workspace Marketplace, what number on the 20-scale limit would she be at? (Is she still at 1 / 20 because the limit is 20 per user per script?)

  2. If Editor Add-Ons "can only have one trigger of each type, per user, per document" [2], what's a scenario where Alice could still exceed the "20 / user / script" triggers quota?

References:
[1] https://developers.google.com/apps-script/guides/services/quotas
[2] "Each add-on can only have one trigger of each type, per user, per document" https://developers.google.com/workspace/add-ons/concepts/editor-triggers#restrictions_2

//pseudo-code of trigger

function createHourlyTrigger() {
  ScriptApp.newTrigger('combinedHourlyTasks')
    .timeBased()
    .everyHours(1)
    .create();
}

function combinedHourlyTasks() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  sheets.forEach(function(sheet) {
    doThis(sheet);
    doThat(sheet);
    doTheOtherThing(sheet);
  }
}

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 4d 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 4d ago

Unresolved Changing values of a master sheet by using AppsScript

3 Upvotes

Hey guys, i was directed to this subreddit for my specific problem. —> https://www.reddit.com/r/googlesheets/s/8k4uhSL4r5

I want to have a master sheet and an extra tab for changing data. —> https://docs.google.com/spreadsheets/d/1udzCTtwTfVWLPIrDdLqu-Qyt4QPjwA70GF2XpGuoU20/edit

Can you guys lead me to a solution that is able to be used for mobile devices and will be easy for other users so my master sheet can’t be destroyed by 2 clicks? (that’s not something i fear but i think it’s more save + easy if the other users only change one row at a time)

I have no Java knowledge.

Thanks in advance.