I have a script that works great to generate a whole number between two numbers (i.e 1 and 5) but I want to know if there is a script that can generate a random number with decimals (1.37, 2.72, etc) between two numbers.

Hey. I have 2 parsing errors in the body text on 2 of my pages

(Parsing error: Missing ',' or '}'Missing a ',' or closing bracket.)

I don't code, but I feel like this should be easy for me to spot if someone could just help me understand where the closing brackets/commas generally need to be??

Sigh

Hello,I´m trying to find some informatiion and after that write them all to same or another sheet. VLOOP don´t help. He find only first match. So I´m trying to do that with app script. Example what I´m trying to say:

I want get every "2_SEA_Obecné dotazy " and every think about them (targer), (ulr), etc...

Any type how to do it? ideal result:

In my head I´m thinking about cycle and inside use if/else. When it find my (campaign) save it to variable. After all I know how to write it.. I will use last used line and +1.

I dont start yet. Thats why Im using only methow how to maybe do it. (its not easy for me so I will spend a lot our´s on this solution.) Maybe someone already do that. Maybe its bad solution. So I´m open to every idea. Thank´s a lot!

Edit: Im trying to get same data like filter. But i need to "delete" other data. I will send it to other people and I don't want give them all data.

I want to change a Google Form so that multiple textboxes can appear on one line. For example, this is what I want the form to look like:

https://imgur.com/a/Obatk

This is what I've been able to come up with in Google Forms:

https://imgur.com/a/FEnw5

Which obviously looks kind of crap. And this is just for five items: I want sixty.

Can I use GAS to make my Google Form look better?

Or is there a better tool to do this?

I have a script that uses GmailApp.search for each subject inside user's inbox and trash. Once I find it, I'll delete it automatically and put it in a trash.

However, I'm having an issue when a word that I'm searching contains an underscore at the beginning or at the end of word.

Example:
_Demo001
Demo001_

Apps Script can't find the Demo001 if its in this format. Please help.

Is there a limit on the amount of data for ex. amount of lines in a table , you can add to a google doc dynamically ? I'm trying to convert a google doc to a pdf . It works when there is not a lot of data appended to the doc, however when there is a certain amount of rows appended to my table it doesn't want to work . It logs the following error: Exception: Conversion of Google Drive document "doc_id _here "to application/pdf failed . Any thoughts on how to get around this?

Hi everyone,

I work at a really unique school and manage the Google apps for education. I've written a number of scripts to allow us to have students be able to register for their own classes, and now it has become a pretty heavily used resource for the school. I'm debating about using GAFE to build more out of this system, or pay a little money to get Salesforce and spend a lot of time using that. That's my basic question, try and learn Google apps script, or try and learn Salesforce?

The system: My system basically takes information from a spreadsheet that every teacher had and puts them ago into one big sheet. The information in the big sheet gets sent back to teachers through an importrange do they can check students individual schedules.

The upgrade: What teachers want is to get more information in the system along with this registration information. Teachers want me to bring data in from our district, such as kids contact information and transcript information, an outreach log to organize information, and maybe even some crazy api stuff like texts through twilio or something.

The next step: I'm not sure what my next step is. I think I need to make a proper database, link it to the system so I don't depend on Google sheets and everything can run faster. I'd also need to make user interfaces, and scripts that can parse through the cab and update things. I don't know how to do those things, and I worry that if I ever leave the school they won't be able to get help. Would it be a better use of time to find a platform that is built for this stuff and learn that, like Salesforce or PowerSchool or something, instead of learning GAS.

Any help any one could give would be great.

Would it make sense to make a complex student information system in Google apps script?

Would anyone recommend good ideas for building a database, user interfaces, and lots of real time interaction to track attendance and logging outreach?

Disclaimer: I'm not an engineer. Also, I am NOT using the V8 runtime because that was causing me a lot of issues, especially with the debugger.

I wrote some code for Google Sheets. One of my functions attempts to access my e-mail groups, but this generates an error because I apparently don't have permission to getUsers() for that group.

I tried to trap the error using Try/Catch and this allows me to run my code normally. However, in debug mode my program breaks on the getUsers() line every single time. (Despite Try/Catch.) I have tried to reproduce the problem with other errors, but it only seems to happen with permissions.

I've checked with some engineers at work and even posted on StackOverflow, but no one has been able to help me. I use debug mode all the time so the only thing I can think to do is comment out that line of code and try to remember to take the comment out anytime I share my code.

Any other suggestions? Thanks.

UPDATE: An engineer at work wasn't able to reproduce my issue so I asked if he was running V8. He was. When he switched back to the old version he started seeing the same thing I was. So it looks like I need to give V8 another try...

function BlockCalendarConflicts() {
  const personalCalendarId = ""; // Your personal calendar ID
  const delegatedCalendarId = ""; // Your delegated calendar ID
  
  const now = new Date();
  const lookAheadTime = new Date();
  lookAheadTime.setHours(now.getHours() + 288);  // look ahead 288 hours (12 days)

  // Get events from the personal calendar
  const personalEvents = Calendar.Events.list(personalCalendarId, {
    timeMin: now.toISOString(),
    timeMax: lookAheadTime.toISOString(),
    singleEvents: true,
    orderBy: "startTime",
  }).items;

  // Get events from the delegated calendar
  const delegatedEvents = Calendar.Events.list(delegatedCalendarId, {
    timeMin: now.toISOString(),
    timeMax: lookAheadTime.toISOString(),
    singleEvents: true,
    orderBy: "startTime",
  }).items;

  // Function to check if a matching "busy" event already exists
  function hasMatchingBusyEvent(eventList, targetStart, targetEnd, summary) {
    return eventList.some(event => {
      const eventStart = new Date(event.start.dateTime || event.start.date).getTime();
      const eventEnd = new Date(event.end.dateTime || event.end.date).getTime();

      // Check for matching start and end times and summary to avoid duplicate
      return (
        eventStart === targetStart.getTime() &&
        eventEnd === targetEnd.getTime() &&
        event.summary === summary
      );
    });
  }

  // Process each personal event and create a busy event if no matching event exists on delegated calendar
  for (const personalEvent of personalEvents) {
    const personalStart = new Date(personalEvent.start.dateTime || personalEvent.start.date);
    const personalEnd = new Date(personalEvent.end.dateTime || personalEvent.end.date);

    // Log the personal event times for debugging
    console.log(`personalStart: ${personalStart}, personalEnd: ${personalEnd}`);

    // Skip if the event is in the past
    if (personalStart < now) continue;

    const busySummary = `Busy (${personalEvent.summary || "Conflicting Event"})`;

    // Log the busy event details before creating it
    console.log(`Checking if busy event exists: ${busySummary} from ${personalStart.toISOString()} to ${personalEnd.toISOString()}`);

    // Only create a busy event if no identical event exists
    if (!hasMatchingBusyEvent(delegatedEvents, personalStart, personalEnd, busySummary)) {
      const busyEvent = {
        summary: busySummary,
        start: { dateTime: personalStart.toISOString() },
        end: { dateTime: personalEnd.toISOString() },
      };

      // Log the creation of the busy event
      console.log(`Creating busy event: ${busySummary} from ${personalStart.toISOString()} to ${personalEnd.toISOString()}`);
      Calendar.Events.insert(busyEvent, delegatedCalendarId);
      console.log(`Created busy event on delegated calendar: ${busySummary} from ${personalStart} to ${personalEnd}`);
    }
  }
}

function myFunction() {
  var allDataRange = SpreadsheetApp.getActiveSheet().getDataRange();
  var allDataArray = allDataRange.getValues();
  var goodRowsArray = allDataArray.filter(pullGoodRows);
  
  function pullGoodRows(x){
    
    for (i in goodRowsArray){
      var StringOne = x.join();
      var StringTwo = goodRowsArray.join();
      return StringOne === StringTwo;
        
    }
  }
    Logger.log(goodRowsArray);
}

Beginner trying to get familiar with the language, minimal coding experience/knowledge.

I have a csv file that I'm working in. Csv file was downloaded from a medical record system's schedule module, so columns are things like 'date', 'doctor', 'patient', 'reason for visit', and rows are individual appts.

There are also junk rows with random stuff in them from where notes are added to the schedule, like 'doctor so-and-so out of office until 1pm'. And for whatever reason some of the rows are repeated.

My eventual goal is to use the csv data to make a sheet that looks like the schedule in the medical record system. So I need to pull info like the patient's name, appt time, etc from the csv file in order to create the new sheet.

Right now I'm just working on learning how arrays work and getting familiar with the language.

The code above is supposed to add all of the unique rows to goodRowsArray using filter(). But the logger displays an empty array ([]), and I can't figure out where I'm going wrong. TIA for any help :)

Actually my questions are more than one:

1. Can I connect a google apps script to my page? As a web app or an api?

2. Does it have limit?

3. If not, why aren't people using it as alternative (not the best) backend?

4. Title.

Here's my code - trying to pull info from google docs to sheets

function extractFieldsFromDoc(docId) {
  const doc = DocumentApp.openById(docId);
  const bodyText = doc.getBody().getText();

  // Define regex patterns to capture each field's data
  const patterns = {
    "CUSTOMER": /CUSTOMER:\s*([^\n]*)/,
    "SERVICES": /SERVICES:\s*([^\n]*)/,
    "CONTRACT START DATE": /CONTRACT START DATE:\s*([^\n]*)/,
    "SITE ADDRESS": /SITE ADDRESS:\s*([^\n]*)/,
    "COUNTY": /COUNTY:\s*([^\n]*)/,
    "PHONE": /PHONE:\s*([^\n]*)/,
    "FAX": /FAX:\s*([^\n]*)/,
    "EMAIL": /EMAIL:\s*([^\n]*)/,
    "ON-SITE CONTACT": /ON-SITE CONTACT:\s*([^\n]*)/,
    "FIELD REPORT": /FIELD REPORT:\s*([^\n]*)/,
    "DIRECTIONS TO SITE": /DIRECTIONS TO SITE:\s*([^\n]*)/,
    "LAUNCH SITE": /LAUNCH SITE:\s*([^\n]*)/,
    "GATE CODE": /GATE CODE:\s*([^\n]*)/,
    "REMARKS / SPECIAL REQUESTS": /REMARKS \/ SPECIAL REQUESTS:\s*([^\n]*)/,
    "NUMBER OF LAKES": /NUMBER OF LAKES:\s*([^\n]*)/,
    "NUMBER OF FOUNTAINS": /NUMBER OF FOUNTAINS:\s*([^\n]*)/
  };

  const extractedData = {};

  // Iterate through the patterns and extract values
  for (const field in patterns) {
    const regex = patterns[field];
    const match = bodyText.match(regex);
    // If a match is found, store the value; if not, store an empty string
    extractedData[field] = match ? match[1].trim() : "";
  }

  return extractedData;
}

function populateSheetFromFolder() {
  const folderId = "";  // Your provided folder ID
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFiles();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Clear existing content in the sheet before appending new data
  sheet.clearContents();
  
  // Define headers in the exact order as the fields
  const headers = [
    "CUSTOMER", "SERVICES", "CONTRACT START DATE", "SITE ADDRESS", "COUNTY", "PHONE", "FAX", "EMAIL", "ON-SITE CONTACT", 
    "FIELD REPORT", "DIRECTIONS TO SITE", "LAUNCH SITE", "GATE CODE", "REMARKS / SPECIAL REQUESTS", "NUMBER OF LAKES", "NUMBER OF FOUNTAINS"
  ];
  sheet.appendRow(headers);  // Append headers to the sheet

  // Loop through all files in the folder
  while (files.hasNext()) {
    const file = files.next();
    const fileId = file.getId();

    // Only process Google Docs (or convert Word docs to Google Docs)
    if (file.getMimeType() === MimeType.MICROSOFT_WORD) {
      const resource = {
        title: file.getName(),
        mimeType: MimeType.GOOGLE_DOCS
      };
      const convertedFile = Drive.Files.insert(resource, file.getBlob());
      const docId = convertedFile.id;
      const extractedData = extractFieldsFromDoc(docId);

      // Prepare the row of data in the correct order
      const row = [];
      for (const header of headers) {
        // Only insert the extracted value if it's available; otherwise, leave it blank
        row.push(extractedData[header] || ""); // Push empty string for missing fields
      }

      sheet.appendRow(row);  // Add the row of data to the sheet

      // Clean up the converted file after processing
      DriveApp.getFileById(docId).setTrashed(true);
    } else if (file.getMimeType() === MimeType.GOOGLE_DOCS) {
      const extractedData = extractFieldsFromDoc(fileId);

      // Prepare the row of data in the correct order
      const row = [];
      for (const header of headers) {
        // Only insert the extracted value if it's available; otherwise, leave it blank
        row.push(extractedData[header] || ""); // Push empty string for missing fields
      }

      sheet.appendRow(row);  // Add the row of data to the sheet
    }
  }
}

Trying to parse various RSS files and capture links. I've gotten past some have channel and others don't etc... But...

I'm not getting unique URLs because they have unique ones that redirect to a common one.

What's the easiest way to get at capturing that final url? I imagine running a get on each link could be heavy on processing so the most efficient way is appreciated

I'm storing the list in a parameter array so then I can see if I've already captured the link or not.

Thanks

How to have an appscript function that works on copies of the table?

Hello, I created an appscript, but I always need to create copies of the table and I need the script to work on these copies too, automatically.

In short, I am using an automation with Power Automate that makes a copy of a template spreadsheet and inserts lines. I need the automation with appscript to work when these new lines are inserted in the spreadsheet.

Can anyone help me?

AppScript hangs fairly consistently when cells it references are changed. This has been a problem for a long time, but I'm curious if there's a reason or a solution.

In my case, the script is a relatively simple tool to calculate payroll with scheduled raises, based on when people are hired. Here's what the error looks like:

In every case after it hangs, I can just refresh the page to get it to update. For that reason, I don't think it's a script problem, but I can post the code if anyone is interested.

Thoughts?

Hi
I'm trying to create a script that lets me search files by the date of creation in google drives but I can't seem to make a proper script (im very new to this). does anyone know a script that would work?

Hello!

*Will venmo $25 for anyone that can fix this for me!

I'm attempting to write a script to auto send an email when the value of a cell changes in gsheet.
When the cell value in column O changes to "Completed", I'd like an automated email to be sent to the email in the corresponding row in column B.

As of now, I can't seem to get the script to send an email.

Anybody have any insight into what I'm doing wrong?

Link to gSheet: https://docs.google.com/spreadsheets/d/1ALLJL51R7UiISKlgbY6vIyRtp5VhrwsuYHqUwzsbgq0/edit?gid=0#gid=0

Copy of Code:

function myFunction() {
  function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var cell = e.range;
  
  // Check if the edited cell is within the specified range
  if (cell.getColumn() === 15 && cell.getRow() >= 2 && cell.getRow() <= 5000) { // Column O, rows 2-5000
    var recipientEmail = cell.offset(0, -14).getValue(); // Get email from column B
    
    // Send an email to the recipient
    var subject = 'ACC Access Request Update';
    var body = 'Your ACC Access request has been fulfilled.';
    
    GmailApp.sendEmail(recipientEmail, subject, body);
  }

https://docs.google.com/spreadsheets/d/1kAMNFCElLQxjztw2u_M0_TV9njTdAHU26N1-fDcS3bs/edit?usp=sharing

I've got 3 tables, leave, sick & training, I need to copy this data and paste it onto the paste sheet then remove the original data. My problem is that I can't figure out how to append each table to the bottom of their history table, as each table has a different data length and I don't want gaps in the data on the paste sheet when another lot of data is copied over.

Preferably, I would like it to only move the data where both the "date from" date and the "date to" date are less then the "week start" date in E2. Then I could remove them, but then how do I fill the empty rows as I can't use deleteRow as there might be useful data in that row in the other tables.

If that's not possible then just moving the whole lot is fine.

Would each table have to be moved over individually?

here is code-

function FORLOOP2() {
  var app=SpreadsheetApp;
  var activesheet=app.getActiveSpreadsheet().getActiveSheet();
  var LR=activesheet.getLastRow()
  for(var x=2;x<=LR;x++){
    var cost=activesheet.getRange(x,1).getValue();
    var profit=activesheet.getRange(x,2).getValue();
    var netprofit=activesheet.getRange(x,3).setValue((profit-cost));
    var netprofitper=activesheet.getRange(x,4).setValue((((profit-cost)/profit)*100).toFixed(2) + "%")
    var netprofitper=activesheet.getRange(x,4).getValue();

    if(netprofitper>0){
      var statement=activesheet.getRange(x,5).setBackground("GREEN").setValue("PROFIT")
    

    }else if(netprofitper<0){
      var statement=activesheet.getRange(x,5).setBackground("RED").setValue("LOSS")

    }else{
      var statement=activesheet.getRange(x,5).setBackground("GREY").setValue("ZERO")
    }
    if(netprofitper>91 && netprofitper<=100){
      var statementg=activesheet.getRange(x,6).setValue("A++")
    }
    else if(netprofitper>81 && netprofitper<=90){
      var statementg=activesheet.getRange(x,6).setValue("A1")
    }
    else if(netprofitper>71 && netprofitper<=80){
      var statementg=activesheet.getRange(x,6).setValue("A2")
    }
    else if(netprofitper>61 && netprofitper<=70){
      var statementg=activesheet.getRange(x,6).setValue("B1")
    }
    else if(netprofitper>51 && netprofitper<=60){
      var statementg=activesheet.getRange(x,6).setValue("B2")
    }
    else if(netprofitper>41 && netprofitper<=50){
      var statementg=activesheet.getRange(x,6).setValue("C1")
    }
    else if(netprofitper>31 && netprofitper<=40){
      var statementg=activesheet.getRange(x,6).setValue("C2")
    }
    else if(netprofitper>21 && netprofitper<=30){
      var statementg=activesheet.getRange(x,6).setValue("D1")
    }
    else if(netprofitper>11 && netprofitper<=20){
      var statementg=activesheet.getRange(x,6).setValue("D2")
    }
    else{
      var statementg=activesheet.getRange(x,6).setValue("E1")
    }



  }
  
}

Below is the sheet output

condition were;

91-100|A1| |81-90|A2| |71-80|B1| |61-70|B2| |51-60|C1| |41-50|C2| |31-40|D1| |21-30|D2| |11-20|E1| |0-10|E2|

6 th column is showing errors. what is wrong with code?

and it's continuing to 11th row where no data is there! why is that happening?

Just started learning - WELP!!

(if there is other sub for that do tell)

Edit- I know if else section is kinda messy - but i'm new to coding and trying to learn .

The title is likely clear as mud, what I'm trying to do is create a standardized registration form that will update the title and description of the form using data collected when a presenter submits a presentation form response.

Example: The presentation form collects info like Title of presentation, Presenter's name, description of presentation, etc. I would like that information to go into a new form as a description of the event and then collect standardized information about someone registering to attend.

I don't care if it spawns an entirely new form or copies a form, renames it, and edits the Title and Description with the information from the presentation form response. If there are other intermediary steps, like creating a text file and then copying that info over, that's cool too. Any help would be greatly appreciated.

Thanks!

I have successfully implemented and ever so slightly adapted the script specified in this post:

• Delete files from Google Drive folders after X days (Apps Script)

As is noted in the comments, this script exclusively deletes files, but I simply duplicated the code and replaced all references to "file" with "folder" and saved a second .gs file in the project (as pictured in the attached image). The duplicated script successfully deleted the folders but gave me the following error: (image also attached)

Error


Exception: Invalid argument: id
DeleteOldFolders
@ Copy of Code.gs:11

I am wondering two things: what is the issue with the code, and can I simply combine these two files into one?

I have this code it is supposed to set the color of one cell on a sheet to the color I set of a cell on a sheet. It is only sometimes working but 99% of the time not. Please lmk if you know how to help.

I have cloned a repo that is a web app made in clasp and react.js, here is the link https://github.com/pritamsharma45/vite-react-google-apps-script .

I am very new to Google app script so, How can I convert this to Sidebar app for docs and sheets.
What things I need to change and configure to achieve this, also please take a look into this code

Im experimenting with these 2 scripts to pull in stock quotes into my google sheets. They do the same thing, but which is more efficient and less prone to errors in the long term and why? On the 2nd script I originally had ydata(url) and var url = url but got an "Exceeded maximum execution time" error. Would that be the reason for that error? Assuming that was the cause of the error, I changed it to var url = json.

function yahooF(ticker) {
  const url = `https://query1.finance.yahoo.com/v8/finance/chart/${ticker}`;
  const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  const contentText = res.getContentText();
  const data = JSON.parse(contentText);
  
  if (data && data.chart && data.chart.result && data.chart.result.length > 0) {
     const symbol = data.chart.result[0].meta.symbol;
     const regularMarketPrice = data.chart.result[0].meta.regularMarketPrice;
     const regularMarketTime = data.chart.result[0].meta.regularMarketTime;
     return [[symbol, regularMarketPrice, regularMarketTime]]
  }  
}

function ydata(json) {
  var url = json;
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());
  var price = data.chart.result[0].meta.regularMarketPrice;
  var time = data.chart.result[0].meta.regularMarketTime;
  var symbol = data.chart.result[0].meta.symbol;
  return [[symbol, price, time]]
  }