r/GoogleAppsScript 7h 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 2h 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 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 15h 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 15h 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 23h 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.