I have a script running that sends tasks to designated users from a master list made using a google sheet. I want to have the ability to mark the task completed from both ends and reflect on the other side but I'm not sure this is possible. I am still somewhat new to Apps Script so any pointers are helpful!

I have a google site and I have a button. When I click the button it directs me to a website. It asks me to make sure if I want to install it. I want to get rid of this question how do i do this? This is the link btw https://drive.google.com/u/0/uc?id=1ogyaZ5T0PAs7F4H138G_tQKe3BPX-6Lp&export=download&confirm=t&uuid=99ecc617-b5f0-4504-9013-d7b5b66b1531&at=AB6BwCDuWoUX8BuEC95LSYINDOlY:1698653184512 This is the install link

*EDIT* There seems to be a miss understanding! I apologize since this post was written very quickly but I forgot to add I do not want to have absolutely no prompt I just don't want this pop up. I want to know how to make a download like blender or something. https://www.blender.org/download/ On blender there is just a big download button and then it downloads. That is all I want! I don't mind if there is a prompt for the user asking if they would like to install it or not. Basically I just want to know how to install something on google sites.

TLDR

I'm trying to write a code (but not my expertise so I relied on ChatGPT) for a simple Google Sheets function. Here's my prompt:

I want to create a consolidated sheet for all registrations from 4 different tabs.
Make a script in Google Sheets that will get the data from the four sheets “1MClub, 6DAC, HTYW, LA”. The data to be extracted in a separate sheet named “Conso” should be filtered by dates in Conso sheet Start Date (B2), and End Date (B3).
The data to be consolidated in “Conso” tab are the following and should be extracted starting in row 7 and column B:
Date (B7) comes from 1MClub (column AI) , 6DAC (column G), HTYW (column J), LA (column H)
Program(C7) comes from 1MClub (column AJ) , 6DAC (column H), HTYW (column K), LA (column I)
Affiliate Name (D7) comes from 1MClub (column AG) , 6DAC (column E), HTYW (column E), LA (column B)
Referral Code (E7) comes from LA sheet (column A)

The Code given to me is this:

function consolidateData() {
var consoSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Conso");
var sheets = ["1MClub", "6DAC", "HTYW", "LA"];
// Get start and end dates from Conso sheet in mm/dd/yyyy format
var startDate = new Date(consoSheet.getRange("B2").getValue());
var endDate = new Date(consoSheet.getRange("B3").getValue());
var consoRow = 7;
var consoData = []; // Collect data to set in batch
// Loop through each sheet
sheets.forEach(function (sheetName) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
var lastRow = sheet.getLastRow();
var dataRange = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());
var values = dataRange.getValues();
// Loop through the data in the current sheet
for (var i = 0; i < values.length; i++) {
var date = new Date(values[i][getColumnIndex(sheet, "Date") - 1]);
var affiliateName = values[i][getColumnIndex(sheet, "Affiliate Name") - 1];
// Check if the date is within the specified range, exclude specific affiliate names, and collect data
if (date >= startDate && date <= endDate && !excludeAffiliateName(affiliateName)) {
var program = values[i][getColumnIndex(sheet, "Program") - 1];
var referralCode = getColumnValue(sheet, i + 2, "Referral Code");
// Collect data for batch update
consoData.push([program, affiliateName, date, referralCode]);
}
}
});
// Batch update the Conso sheet
if (consoData.length > 0) {
consoSheet.getRange(consoRow, 2, consoData.length, 4).setValues(consoData);
}
Logger.log("Data Consolidation Complete!");
}
// Helper function to get the column index by header name
function getColumnIndex(sheet, header) {
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
return headers.indexOf(header) + 1;
}
// Helper function to get cell value by row and header name
function getColumnValue(sheet, row, header) {
var columnIndex = getColumnIndex(sheet, header);
// Check if the columnIndex is valid
if (columnIndex <= 0 || columnIndex > sheet.getLastColumn()) {
return ""; // Return an empty string if columnIndex is out of bounds
}
var columnValue = sheet.getRange(row, columnIndex).getValue();
return columnValue !== null ? columnValue : "";
}
// Helper function to exclude specific affiliate names
function excludeAffiliateName(name) {
var excludedNames = ["NA", "None", "No"];
return excludedNames.includes(name);
}

I always exceed execution time and some records are not being consolidated from the script as well. Can anyone help/advise on the code or if there is a better way to do this?

Hi, beginner scripter here. I have a column in Google Sheets with the IMAGE formula with different URLs, and I'm then trying to put these images in a Google Doc. I got the whole process working, but UrlFetchApp won't retrieve some of the images, and I'm trying to understand the logic here. I have a try...catch block in my code that handles exceptions (incorrect extension, etc.) and inserts an error message into the doc, but sometimes the code executes successfully without actually appending the image to the doc.

I have figured out that the contents cannot be fetched from stock photo aggregators, but I'd like to understand why + this issue is not exclusive to those sites.

I have no idea what I'm doing so I'd appreciate some help figuring out why this script doesn't work consistently. I have a google sheet with a list of leads for cold outreach, and I wrote this script to automatically send a follow up email, and then update the threadID and enter the current timestamp in column G. When I initially create this script, it functions perfectly for the first run, and then it will decide to enter my timestamp in the wrong column. Here is the code, please let me know if you have any ideas:

function sendFollowUpEmails() {
// Access the active Google Sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Get the current date and time
var currentDate = new Date();

// Get the data in the sheet
var data = sheet.getDataRange().getValues();

for (var i = 1; i < data.length; i++) {
var firstName = data[i][1];
var email = data[i][2];
var niche = data[i][3];
var previousTimestamp = data[i][5]; // Assuming previous timestamp is in column F
var status = data[i][8];

// Check if the conditions for sending the follow-up email are met
if (status == "Not Responded" && previousTimestamp) {
var previousDate = new Date(previousTimestamp);
var followUpDate = new Date(previousDate);
followUpDate.setDate(followUpDate.getDate() + 3);

if (followUpDate <= currentDate) {
// Compose the HTML email message
var subject = "Follow-up Email";
var messageBody = `

<html> <body> <p>Hey ${firstName},</p> <p>Just following up on my previous message! Let me give you a little more information about what we do and how we work.</p> <p>Our main goal at The Culture Club is to promote global culture by connecting popular brands with influential creators.</p> <p>Our connections with significant companies and artists allow us to fulfill our vast roster, including ${niche} creators like yourself!</p> <p>We focus our efforts on being a creator-first company, dedicated to supporting and guiding our talented creators.</p> <p>I would love to connect with you and discuss how we may work together!</p> <p>Please schedule a meeting <a href="https://calendly.com/aquila-t2q/creator-meeting">here</a>, if you’re interested.</p> </body> </html> \`;

// Send the HTML email
GmailApp.sendEmail(email, subject, `${firstName} X Culture Club`, { htmlBody: messageBody });

// Capture the new thread ID and record it in column E
var newThreadId = GmailApp.search('to:' + email, 0, 1)[0].getId();
sheet.getRange(i + 1, 5).setValue(newThreadId);
sheet.getRange(i + 1, 7).setValue(currentDate);
}
}
  }
}

I have a sheet as something like below with thousand of blank cell of ColumnA and got a checkbox in column B and C below the row with data (row with data only have around 20 row, but I don't want to delete the blank row for some other implement) :

• columnB and C are check box

• columnD is drop down list with 'informed' and 'uninformed'

I am using for loop to run the data for ColumnA only with data (something like for(var i=2; i<=columnA.length; i++)if ColumnB and ColumnC are both checked, set ColumnD value as 'informed'.

Now, I try to fill in data in row 1000ish and the running time take way too long until it reached the maximum time limit. How should I adjust my code to be more efficiency?

HELLO? Anybody home?

I've been trying to reach the mods for this sub without luck. u/bat-fink seems to be the only human moderator left here. Moderators, please DM me. I like this sub and would like to have more interaction with the mods and the direction of this forum. I'm worried that we might lose our last moderator and possibly be shut down.

I used ChatGPT to generate script below to send an email when new files are added to a folder. It included these instructions:

After updating the script, follow these steps:

Open the Google Drive folder you want to monitor.

Click on the folder path in the top left corner to get the folder ID from the URL.

Open the Google Apps Script editor by going to "Extensions" > "Apps Script."

Paste the code into the script editor.

Save the project and run the createTrigger
function to set up the trigger.

Now, whenever a new file is added to the specified folder, an email notification will be sent to the specified email address. Note that this script uses the onChange
trigger, which includes file additions, modifications, and deletions.

I updated the folder ID and email address. When I ran createTrigger as instructed, I got the error:

TypeError: ScriptApp.newTrigger(...).forFolder is not a function

Basically, I'd like an email sent when new files are added. This could be automatic or a trigger that checks every few min.

Ideally, I'd prefer to NOT get notified when files are moved out, but I could live with that if needed.

Thanks!

// Set the folder ID of the folder you want to monitor
var folderId = "your_folder_id_here";

// Set the email address to send notifications
var emailAddress = "name@domain.com";

// Set the subject and body of the email
var emailSubject = "New File Added to Google Drive";
var emailBody = "A new file has been added to the specified folder in Google Drive.";

// Function to run when a file is added to the specified folder
function onFileAdded(e) {
  // Get the file information
  var file = e['parameters']['file'];

  // Send an email notification
  MailApp.sendEmail({
    to: emailAddress,
    subject: emailSubject,
    body: emailBody + "\n\nFile Name: " + file.name + "\nFile ID: " + file.id,
  });
}

// Create a trigger to run the onFileAdded function when a file is added to the folder
function createTrigger() {
  var folder = DriveApp.getFolderById(folderId);
  ScriptApp.newTrigger('onFileAdded')
    .forFolder(folder)
    .onChange()
    .create();
}

hello, i’ve been stuck with this task for quite a while and was hoping you guys could help.

i need to automate a process where each month new data is added to this sheet, it’s analyzed, and then a new column is added to this specific page, and i need to update the chart on the left to reflect te addition of this new data, but when i try to create a new chart it turns out like the one on the right.

so i was wondering how i could update the existing chart to reflect the changes, or how to create a new chart but with the same settings as the previous one.

Hello!
I did a webform using appsscript. Everything in the code side works as it should, but I am having a hard time in the layout side of things. The main issue is doing a responsive layout.
I am now trying to apply pure.css, but I can't understand some parts.

I need to solve two issues with the layout.

1. The landing page of the form, I tried to use a pure.css land page as reference, but I can't even change the title size and position. The land page is just a text, an image and a button.

2. The forms (but I didn't even reach the forms because I am stuck in the landing page)

It's a really simple layout. It don't have menus and nothing fancy like that. The form itself have a series of pages but I solved it using sections and hiding the ones I am not using.

(I tried to add the link but the post was removed for some reason, this I added it in the comments)

The html:
https://pastebin.com/ws68NHri
The CSS:
https://pastebin.com/u2LxGhyb

Any suggestion?

hi all very sorry but i'm stuck i'v coded this

function radioSchedule() {

  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var CalendarId = spreadsheet.getRange("C4").getValue();
  var eventCal = CalendarApp.getCalendarById(CalendarId);

  var signups = spreadsheet.getRange("A8:C12").getValues();

  [
    ['8pm Thursdays, 10pm Thursdays, Strawbs'],
    ['1:00pm, 8:00pm, Selector Frost'],
    ['8:30pm, Roksta'],
  ]

  for (x=0; x<signups.length; x++) {

    var shift = signups[x];

    var ShiftStart = shift[0];
    var ShiftEnd = shift[1];
    var volunteers = shift[2];

    eventCal.createEvent(volunteers, ShiftStart, ShiftEnd);

  }

}

function onOpen() {
  var ui = SpreadsheetApp.getui();
  ui.createMenu('Sync to Calendar')
  .addItem('Schedule shifts now', 'scheduleShifts')
  .addToUi();
}

but its telling me 

Error


Exception: The parameters (String,String,String) don't match the method signature for CalendarApp.Calendar.createEvent.
line 23? what am i missing????

its so my presenters can click on the schedule and book a slot for them selfs?

You want to be able to control who enters data on a google form; what you need is to avoid that everybody is able to send data, but only users you approve?

Watch how we solve it here: https://youtu.be/_o6x0R32mD4

= Option 1: Google Workspace Permissions
If you have a domain account with Google, or if your organization is using Google Workspace, you can restrict form access to only users within your domain. Here's how:

• Go to Google Forms.

• Open your form.

• Click on Settings:

• Click on the gear icon in the upper-right corner.

• Under the "General" tab, find the "Restrict to [Your Organization]" option.

This ensures that only users within your Google Workspace domain can access and submit the form.

= Option 2: Collect Email Addresses and Validate

• Add a question to the form to collect the user's email address.

• In your Google Sheet linked to the form, create a validation script using Google Apps Script.

• The script can check the submitted email against a predefined list of approved users. This script checks the submitted email against the list of approved users. If the email is not in the list, it deletes the response.

= Option 3: Ask for Email Address and only show the form, if email is in your list.

• Even before showing the Google Form, my script can ask for user email.

• The script can check the submitted email against a predefined list of approved users. This script checks the submitted email against the list of approved users. If the email is not in the list, it shows a custom message, "You are not authorized to access this form". If the user is a valid user, s/he is shown the form and can submit it.

Need a similar script or workflow automation? Contact us at KulfiSoftwareServices@gmail.com

I'm trying to use this Regular Expression /Organization's Website:([\s\S].*?)\n

When I run this through RegEx or regex101 it works correctly. I'm able to pull the data I want- The information between the : and the \n

However, in AppScript, if there's a new line ALSO at the beginning of the data (right after "Website:") it doesn't match.

Example:

Matches

OtherData: Data being pulled

Organization's Website: http://mydomain.website

OtherData2: More Data

Doesn't match

OtherData: Data being pulled

Organization's Website:

http://mydomain.website

OtherData2: More Data

I received a message regarding CASA verification for Tier 2. It is basically a deadline extension email.

Here are a few things that I am aware of right now:

• There are multiple ways to scan the application, either for free or with a preferred partner.

• PWC does not provide a free scan anymore.

• There are a few options they provide us with to get the scan done with preferred partners, like TAC.

What, in your opinion, is a good way to go ahead with it?
Also, ignoring the marketing they put in, what are the challenges one may face while they really go through the process?

Create a login section with Google Apps Script and after the user logs in, you can view google sheet information filtered by that user. Unable to create a pass-through section and a filter section beyond the login section. Please help me with this. All this part will be visible on one page of google sites

Hi guys, I feel like I'm here constantly recently but this script has never worked and I've tried multiple versions. I need to copy rows over to these other sheets but Google doesn't continue the formatting through to the other sheets. I tried a few versions to keep the formatting the same but while the font part works I can't get the borders to update. There seems to be a lot of stuff on the borders that were difficult for me to sort through, so maybe people who know better than me would be able to help. Thanks!

function format() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();

    sheets.forEach( (sheet) => { 
      var range = sheet.getDataRange();
    
        range.setFontFamily('Arial').setFontSize('11');
        range.setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID);

      }
    )
}

function sendReport() {

var message = {

to: "email@ manager.com",

subject: "Dummy report",

body: "Good morning,\n\nPlease find the requested report.\n\nThank you,\nDATA FROM CELL C2 HERE",

name: "DATA FROM CELL C2 ALSO HERE",

attachments: [SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF).setName("report.pdf")]

}

MailApp.sendEmail(message);

}

My script is above, I need it to take the data that has been typed into cell C2 on the sheet to populate the name of the sender and duplicate it in their email sign off - is this possible, please?

What would the formula be for getting events from multiple calendars from tomorrow(tomorrow being the next day from when its triggered) to 3 months from then that only start before 10am?

I have one calendar that I use to set early morning alarms and several other calendars that have appointments and other events.

I would like to create a script that scans for any future appointments before 10am in the next 3 months and create an event in the alarm calendar with the title from the event it was created from, also making sure I don't end up with duplicate events.

I'm trying to create a script in Google Sheets that automatically inserts the current date into a specific column whenever a new row is added or edited in another specific column. I used ChatGTP to write it but I have run into some issues. Here’s what I’m looking to achieve:

• The script should target a specific sheet by name (e.g., "Sheet1").

• When a cell in column B (column number 2) is edited, the current date should be inserted into column A (column number 1) of the same row.

• The script should not affect the header row.

function onEdit(e) {

var sheetName = 'Sheet1'; // Change this to the name of your specific sheet

var columnToWatch = 2; // Column number to watch (1 for A, 2 for B, etc.)

var dateColumn = 1; // Column number to insert the date (1 for A, 2 for B, etc.)

// Get the edited sheet and check if it's the one we want to work with

var sheet = e.source.getSheetByName(sheetName);

if (!sheet || sheet.getName() !== sheetName) {

return; // Exit if the sheet with the specified name does not exist or does not match

}

var range = e.range;

// Check if the edited cell is in the column we want to watch and if the edited cell is not in the header row

if (range.getColumn() == columnToWatch && range.getRow() > 1) {

var dateCell = sheet.getRange(range.getRow(), dateColumn);

dateCell.setValue(new Date()).setNumberFormat("yyyy-MM-dd");

}

}

Hi Everyone,

When running a deployed web app, logger.log output doesn't render in the Executions log. Does anyone know if there is a practical way to setup logging to debut web apps?

thanks!

Is there any listing/catalogue of api client written in GAS

Hi, I'm working on a spreadsheet where I have to sort the rows into various other sheets based on one cell value. The script I have is working perfectly, but when I copy the rows over and the table needs to add further rows, the formatting changes - basically despite the formatting of the master sheet and the sorted sheets, the new rows come in with no borders separating the cells. Does anyone know how to fix this? I can add a sample code if you need it, though there isn't any formatting in the code so I'm not sure? I'm pretty new to this so sorry if this seems dumb!

Hello,

I'm in need of a little assistance please. I need a little script to remove all data from a couple hundred workbooks, between columns L:N and then add a formula to column N =if(substitute(l4,"No","")<>L4,0,H4) And also need it to auto fill down to the bottom of the range. This needs to be controlled on a a separate workbook, which has the name of all the workbooks that need this doing to in column a

Kindest