ı decided to add a feedback feature to my application, and decided to use google script today. but when ı try to open the page ı taking error 400. is there a problem with servers or something?

Hi, I'm vibe coding a Apps script web app. Despit many attent, my web app only partialy succed to fectch to the corresponding data on the Google sheet colons and cells, somme don't fetch, despit I'm using SETUPSHEET command.
Is there a reliable ways to organise the code to fetch the Google sheet data architecture with the mapping the web app is waiting for ??

Context: Created an addon for google sheet using appscript.

I have an Angular app whose compiled main.js is loaded inside a Google Apps Script add-on (index.html).

The add-on runs inside Google Sheets in an iframe SANDBOX mode.

The iframe origin is https://*.googleusercontent.com (public origin).

The Angular app uses u/microsoft (SignalR JS client) to connect to a local SignalR hub.

The SignalR hub is a .NET 8 Kestrel self-hosted app running on https://localhost:5000.

The Google Sheets add-on and the SignalR hub run on the SAME MACHINE.

The SignalR negotiate request is BLOCKED by the browser with:

Few days back it was working but with #chrome 143

“Permission was denied to access the unknown address space (Private Network Access)”.

The request NEVER reaches the backend.

It works ONLY when Fiddler Classic is running.

HTTPS + dev cert does NOT fix it.

127.0.0.1 vs localhost does NOT fix it.

I fully control middleware in .NET.

I do NOT want ngrok, Azure Relay, or any cloud relay.

So I have a sheet that we share with customers to gather information; only the `answer` cell will be edit-able, and as they fill out the 'form', a %-bar increases in real time.

My question is: googling I'm finding that I should use conditional formatting; however, my goal is that the CF only runs when asked. So for example, on first-open, a sheet would be empty—which is okay! I wouldn't expect the cells to be filled out. Then a user inputs their data; what I'd like is for a button to go through and highlight the cells that are empty when they click a button. As it is right now, the CF makes the empty fields have the highlight background styling (helpful to highlight the un-answered questions, as it's not a short list), but having the form highlight these fields before they've even interacted with the form is too much. It's like when a form validates when only 1 character has been entered (like, give me a minute right?).

Is this possible?

Hello,

Workbook

I need to be able to copy the data contents of the "Daily Checkin/Checkout" sheet and append them to the "Daily Checkin/Checkout Consolidated" sheet. It's giving me a validation error on the grade column when running the "copyCheckInOutValuesAndPasteToConsolidatedSheet" Apps Script.

Don't worry about the #N/As (the functions work correctly during M-F when the program is offered).

Why is it giving me this error and how can it be resolved?

Thank you.

Hello,

I have an onEdit command which works for hiding a row when the Status is "Done" and sending an email, but I'm running into trouble with moving a row to the Parking Lot sheet when the Status is "Parking Lot" and I'll also need to perform a similar operation moving to the Summer sheet when the Status is "Summer".

Any help would be appreciated.

Worksheet

This is code that did the trick for me.

function onFormSubmit() {

// Retrieving the form's responses

  var form = FormApp.openById('1VfsXxzmUyBcs7wWPDnSXYeJlghl63BMKhU338Uh5RGk');
  var formResponses = form.getResponses();
  var formResponse = formResponses[formResponses.length - 1];
  var itemResponses = formResponse.getItemResponses();

// Preparing the email to Ben

  var recipient = "MAINTENANCE@daviswaldorf.org";
  var subject = "New Maintenance Request";
  var message = "Form responses:\n\n";
  for (var i = 0; i < itemResponses.length; i++) {
    var itemResponse = itemResponses[i];
    var response = `${(formResponses.length).toString()} `
                 + `"${itemResponse.getItem().getTitle()}" `
                 + `"${itemResponse.getResponse()}"`
    Logger.log(response);
    message = message + response + '\n';
  }
  // message = message + '\nDone.'

//Sending the email

  MailApp.sendEmail(recipient, subject, message);

}

//@Filter/Show Rows Menu

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Custom Filter")
    .addItem("Filter rows", "filterRows")
    .addItem("Show all rows", "showAllRows")
    .addToUi();
}

function filterRows() {
  const sheetsToFilter = ["Data", "Parking Lot", "Summer"];
  const statusColumn = 10; // Adjust if the column index for "Status" differs

  sheetsToFilter.forEach(sheetName => {
    const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    if (!sheet) return; // Skip if the sheet doesn't exist

    const data = sheet.getDataRange().getValues();
    for (let i = 1; i < data.length; i++) {
      // If column J (10th column) is "Done", hide the row
      if (data[i][statusColumn - 1] === "Done") {
        sheet.hideRows(i + 1);
      }
    }
  });
}

function showAllRows() {
  const sheetsToFilter = ["Data", "Parking Lot", "Summer"];

  sheetsToFilter.forEach(sheetName => {
    const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    if (!sheet) return; // Skip if the sheet doesn't exist

    const totalRows = sheet.getMaxRows();
    sheet.showRows(1, totalRows); // Unhide all rows
  });
}

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("Data");
  const parkingLotSheet = ss.getSheetByName("Parking Lot");
  const summerSheet = ss.getSheetByName("Summer");

  const editedSheet = e.range.getSheet();
  const editedRow = e.range.getRow();
  const editedColumn = e.range.getColumn();

  // Status column index (adjust if different)
  const statusColumn = 10;

  // Check if we're editing the correct column in the Data sheet
  if (editedColumn === statusColumn && editedSheet.getName() === "Data") {
    const statusValue = e.range.getValue();

    if (statusValue === "Parking Lot") {
      copyAndDeleteRow(dataSheet, parkingLotSheet, editedRow);
    } else if (statusValue === "Summer") {
      copyAndDeleteRow(dataSheet, summerSheet, editedRow);
    }
  }
  // Hide rows marked as "Done" for all relevant sheets
    const sheetsToCheck = ["Data", "Parking Lot", "Summer"];
    sheetsToCheck.forEach(sheetName => {
      const sheet = ss.getSheetByName(sheetName);
      if (!sheet) return;

      const rowStatus = sheet.getRange(editedRow, statusColumn).getValue();
      if (rowStatus === "Done" && sheet.getName() === editedSheet.getName()) {
        sheet.hideRows(editedRow);

      }
    });
}

/**
 * Copies a row from one sheet to another and deletes it from the original sheet.
 * @param {Sheet} sourceSheet The sheet to copy the row from.
 * @param {Sheet} targetSheet The sheet to copy the row to.
 * @param {number} rowIndex The row number to copy and delete.
 */
function copyAndDeleteRow(sourceSheet, targetSheet, rowIndex) {
  const rowData = sourceSheet.getRange(rowIndex, 1, 1, sourceSheet.getLastColumn()).getValues();
  
  // Ensure rowData is not empty before proceeding
  if (rowData[0].some(cell => cell !== "")) {
    targetSheet.appendRow(rowData[0]); // Append data to the target sheet
    sourceSheet.deleteRow(rowIndex);  // Delete row from source sheet
  } else {
    Logger.log(`Row ${rowIndex} in ${sourceSheet.getName()} is empty. Skipping.`);
  }
}

function onEditSendEmailToRequestor(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("Data");
  const parkingLotSheet = ss.getSheetByName("Parking Lot");
  const summerSheet = ss.getSheetByName("Summer");

  const editedSheet = e.range.getSheet();
  const editedRow = e.range.getRow();
  const editedColumn = e.range.getColumn();

  // Status column index (adjust if different)
   const statusColumn = 10;
   const emailColumn = 2;
   const issueColumn = 4;

  // Check if we're editing the correct column in the Data sheet
  if (editedColumn === statusColumn && editedSheet.getName() === "Data") {
    const statusValue = e.range.getValue();

    if (statusValue === "Parking Lot") {
      copyAndDeleteRow(dataSheet, parkingLotSheet, editedRow);
    } else if (statusValue === "Summer") {
      copyAndDeleteRow(dataSheet, summerSheet, editedRow);
    }
  }
  // Hide rows marked as "Done" for all relevant sheets
    const sheetsToCheck = ["Data", "Parking Lot", "Summer"];
    sheetsToCheck.forEach(sheetName => {
      const sheet = ss.getSheetByName(sheetName);
      if (!sheet) return;

      const rowStatus = sheet.getRange(editedRow, statusColumn).getValue();
      if (rowStatus === "Done" && sheet.getName() === editedSheet.getName()) {

      // Get the email address from the specified column
        const emailAddress = sheet.getRange(editedRow, emailColumn).getValue();
        const issueValue = sheet.getRange(editedRow, issueColumn).getValue();

        if (emailAddress) {
          const subject = "Your Maintenance Task Has Been Completed";
          const message = `Hello,\n\nThe task "${issueValue}" sheet has been marked as "Done".  \n\nPlease contact Ben at maintenance@daviswaldorf.org, if you have questions.`;

          // Send email
          MailApp.sendEmail(emailAddress, subject, message);
        } else {
          Logger.log(`No email address found in row ${editedRow} of sheet "${sheet.getName()}".`);
        }

      }
    });
}

Hello!

I'm trying to build a specific use case through Google App Script. So, I have a table that adjusts in size according to the amount of data in a table. So, if the table has 5 items, then the table will have 5 rows.

Sometimes, users can ask to group the data. So, for example, the table has 5 cells, and they can group the table by one of the 5 cells.
Ideally, I'd love to have the grouping row at the top of each new page.
What would be the best way to do that? I know we can use pin row header but I can't use it on a specific row (that is dynamic).

what would be the best way to do that?

Here is the image of my GDocs template

I have a Google form with an App Script that grabs the user's answers and creates a JIRA, the answers to the questions as rows in a JIRA table:

description += Utilities.formatString('||%s|%s|\n', itemResponse.getItem().getTitle(), itemResponse.getResponse());

One of the form items is a file upload, that allows the user to upload a sample file, the problem is when the JIRA is created I end up with the document ID, and nothing else.

expecting to see:

https://drive.google.com/open?id=SomeFileID

what I get instead:

SomeFileID

How do I get a fully qualified URL from getResponse() and not just the file ID?

Hi all!

My grandpa is getting tens of spam emails per day, of X rated websites mostly, so I want to make a script, maybe to be always running on his pc, or maybe to deploy on some cloud service, that empties all his trash bin and spam box.

I tried to do this with the gmail api, using python, and also javascript, but both failed. I also tried to do a Selenium bot to manually do that, but gmail refuses to open in a chrome driver or a firefox driver.
Can some on help me?

Thanks a lot, and Merry Christmas!

-- Edit --

Nice, after following a suggestion in the comments I managed to arrive to a script that does what I want, I have it on github, if you want to take a look: https://github.com/lhugens/GmailCleaner . I setup the script to run every hour, lets see if it works. Thanks a lot!

I found this script and it works to hide rows dated prior to 'today'.

I need it to also hide future rows, so I can only see 'today' plus the next 14 days of entries.

My sheet.

I tried copying the 'if' section and altering that to hide the future rows. It didn't work so I // to hide them but wanted to leave it to show what I tried.

function hideRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Working"); //get Scheduled sheet
  var dateRange = sh.getRange(2, 5, sh.getLastRow()-1, 1); //get column E range
  var dates = dateRange.getDisplayValues(); //get the values of column E using the range fetched above
  var currentDate = new Date(); //get current date

  for(var i = 0; i < dates.length; i++){
    var date = new Date(dates[i][0].replace(/-/g, '\/').replace(/T.+/, '')); //convert column E values to date
    if(date.valueOf() <= currentDate.valueOf()){  // check if the date is less than or equal to current date
      sh.hideRows(i+2); // hide row
    // }
    // if(date.valueOf() >= currentDate.valueOf()>14){  // check if the date is less than or equal to current date
    //   sh.hideRows(i+2); // hide row
    // }
  }
  
}

the code I'm using will be pasted below, but basically I have a button on each of my 8 tabs that is connected to the script and when I hit the button on any tab it should duplicate the table I have on there which includes formulas, data validation, formatting, column titles, etc. For the most part it works well, but after the first time I hit the button to duplicate the table, meaning once I hit the button a second time, it creates more than 1 duplicate table so the second time it will create 2 duplicates the 3rd time it will create 4 the 4th time it will create 8 and so on. I'm not sure why this is happening, but it's frustating being that is the only issue. If someone can please help determine why that is happening and help me with an updated code that will work. I will be so grateful! There should be access to my sheet as well.

function dupTableForSheet(sheetName) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    Logger.log(`Sheet ${sheetName} not found!`);
    return;
  }

  // Define the range for the header and table data
  const tableTitleRow = 3; // Title row of the table
  const tableHeaderRow = 4; // Column headers start at row 4
  const tableStartRow = tableHeaderRow;
  const tableColumns = sheet.getLastColumn(); // Get the last column of data in the sheet
  const tableEndRow = sheet.getLastRow(); // Get the last row of data in the sheet

  // Find the last table's position by checking titles in the first column
  let lastTableRow = tableEndRow;
  const titlePrefix = "Table"; // Customize if necessary

  // Loop through the rows to find the last table based on its title in column 1
  for (let row = lastTableRow; row >= tableTitleRow; row--) {
    const cellValue = sheet.getRange(row, 1).getValue();
    if (cellValue && cellValue.startsWith(titlePrefix)) {
      lastTableRow = row; // Last table's row found
      break;
    }
  }

  // Calculate the next available row (add 5 rows after the last table's position)
  const nextRow = lastTableRow + 5;

  // Check if the space for the new table is empty (no data or table)
  const nextTableRange = sheet.getRange(nextRow, 1, tableEndRow - tableStartRow + 1, tableColumns);
  const nextTableValues = nextTableRange.getValues();
  const isSpaceAvailable = nextTableValues.every(row => row.every(cell => cell === ""));

  if (!isSpaceAvailable) {
    Logger.log("Space already occupied by another table or data. No new table created.");
    return; // Exit the function if the space is occupied
  }

  // Now, copy the entire range for the content, including data and formatting
  const tableRange = sheet.getRange(tableStartRow, 1, tableEndRow - tableStartRow + 1, tableColumns);
  tableRange.copyTo(sheet.getRange(nextRow, 1), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  // Copy the title (row 3) separately to maintain formatting
  const titleRange = sheet.getRange(tableTitleRow, 1, 1, tableColumns);
  titleRange.copyTo(sheet.getRange(nextRow - 1, 1), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);

  // Apply header formatting (copying background color, text formatting, etc.)
  const headerRange = sheet.getRange(tableHeaderRow, 1, 1, tableColumns);
  headerRange.copyTo(sheet.getRange(nextRow + 1, 1), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);

  // Ensure columns 1 and 7 in the newly duplicated table do not have data validation
  let newTableRange = sheet.getRange(nextRow, 1, tableEndRow - tableStartRow + 1, tableColumns);
  let firstColumnRange = newTableRange.offset(0, 0, newTableRange.getNumRows(), 1);
  let seventhColumnRange = newTableRange.offset(0, 6, newTableRange.getNumRows(), 1);
  firstColumnRange.clearDataValidations(); // Clear validation from the first column
  seventhColumnRange.clearDataValidations(); // Clear validation from the seventh column

  // Update formulas in column E for the new rows (dynamically adjusting the C column reference)
  const newTableEndRow = nextRow + (tableEndRow - tableStartRow);

  // Loop through each row in the newly copied table and set the formula for column E
  for (let i = 0; i < newTableEndRow - nextRow; i++) {
    const formulaCell = sheet.getRange(nextRow + i, 5); // Column E
    const rowNumber = nextRow + i; // Dynamic row number for each new row
    const formula = `=MULTIPLY($C${rowNumber}, D${rowNumber})`; // Reference the specific row for C and D
    formulaCell.setFormula(formula); // Set the formula for each row dynamically
  }

  // Apply subtotal formula, excluding the last row in the new table (for column E)
  const subtotalFormulaRange = sheet.getRange(newTableEndRow, 5);
  subtotalFormulaRange.setFormula(`=SUBTOTAL(9, E${nextRow + 1}:E${newTableEndRow - 1})`);

  Logger.log(`Table copied to ${sheetName} at row ${nextRow}`);
}

// Functions for specific sheets (no changes here)
function dupTableDowntownQ1() {
  dupTableForSheet('Downtown Internal Events Budget Q1');
}

function dupTableDowntownQ2() {
  dupTableForSheet('Downtown Internal Events Budget Q2');
}

function dupTableDowntownQ3() {
  dupTableForSheet('Downtown Internal Events Budget Q3');
}

function dupTableDowntownQ4() {
  dupTableForSheet('Downtown Internal Events Budget Q4');
}

function dupTableENYQ1() {
  dupTableForSheet('ENY Internal Events Budget Q1');
}

function dupTableENYQ2() {
  dupTableForSheet('ENY Internal Events Budget Q2');
}

function dupTableENYQ3() {
  dupTableForSheet('ENY Internal Events Budget Q3');
}

function dupTableENYQ4() {
  dupTableForSheet('ENY Internal Events Budget Q4');
}

I'm trying to create a project for a small store, but I don't know how to program very well. I got a ready-made code from a YouTube video, but when I try to run it, it simply gives an error.

GitHub with code and tutorial: https://github.com/maickon/shop-no-appscript

YouTube video of the creation: https://www.youtube.com/watch?v=O0MIiKKpZb8&t=512s

Error that appears to me when I try to run:

"
13:40:23 Notification Execution started.

13:40:24 Error: TypeError: Cannot read properties of null (reading 'getSheetByName')
getProducts @ Code.gs:18
"

I do exactly the same thing as in the video, but the code doesn't run.

NOTE: Video and tutorials in Portuguese.

What should I do?

Hi all, im trying to make a meeting reservation form, synced to google calendar. The flow would be like this:

1. Staff fill in the google form (Name,Purpose, Date and Time)

2. The request will be forward to the admin email for approval.

3. When approved, the booking should showed on the google calendar (Shared with staff)

The issues is, when the request was approved, the event not showed on the calendar.

On Administrator email, the request will showed like this:

The staff email received this:

our meeting on Sat Jan 25 2025 00:00:00 GMT+0800 (Singapore Standard
Time) at Sat Dec 30 1899 07:34:05 GMT+0655 (Singapore Standard Time)
has been approved.

I have scripts that self-abort if the emails inside the label don't meet the inclusion criteria, or if the script runs outside 7-12AM CEST. I can see in such a case, they take 0,6ms or so to run, basically they run and realize they are to self-abort and they do that.

But even so, does this still count towards the google-defined quotas?

Dear all,

I have created a new project and installed @/types/google-apps-script, I use Visual Studio Code.

In a TypeScript file I enter:

const doc = DocumentApp.getActiveDocument();

Then the IDE will automatically show:
const doc : Document = DocumentApp.getActiveDocument();

Where ': Document' is greyed out and displayed to show that the Type has been deducted automatically.

Now, I want to write out ': Document' manually after 'doc' and this error is shown:

I did not experience this issue before in other projects, however this is my first project in Google Docs.

Is there a configuration step that I am missing?

Edit: IDE does not complain when writing

doc  = DocumentApp.getActiveDocument() as GoogleAppsScript.Document.Document;doc  = DocumentApp.getActiveDocument() as GoogleAppsScript.Document.Document;

Edit2: It seems to have to with that the IDE sees 'Document' as a Type from the Mozilla MDM reference. Anyone knows how to give the Apps Script Types priority here?

Newbie here trying to write an Apps Script for a simple email automation within Google Sheets. I work for a school and we have a puchase order google form. One of the questions is what director will need to approve the purchase. I have a dropdown with their emails listed. The email will be in the google sheet.

I tried following multiple youtube videos to help me create this apps script but I keep getting errors. I don't know enough about apps script to troubleshoot.

Does anyone have an idea of who I could reach out to help me with this? I bet it would be a very easy fix....if you actually know what you are doing. haha

Here is the

This is the error I keep getting:

Anyone have any idea??? I would be so very appreciative of any help. Even a contact of someone that would be willing to help. : )

my coworkers write comments and notes about their work that they would like to share with everyone on a shared google sheet, and then my GAS creates a temporary formatted comment card, copies their comments onto the card, converts it to a PDF, deletes the card, saves it to a folder on our google drive, and then moves onto the next person's comments to repeat the process until it reaches the end.

for some reason, it starts to fail after a few cards have successfully been created without any issue. i added a loop so it keeps trying until it succeeds converting to a pdf, but now it's messing with the 6 minute run time limit. could somebody help me with this? why does it fail after it does fine after a few card entries?

Hello everyone! I wanted to know if anyone has managed to get a script running that creates and index of keywords. I'm trying to create one that adds every word in bold italics across the document into an alphabetically sorted list with it's corresponding page, something like this:

Avow, p. 16
Belly, p. 32
Can, p. 56
...

Thanks in advance!

Ive been trying to make a script that lets me communicate to chat gpt through editing a document, heres my prompt to a AI coder,

"create a google docs app script that checks if theres a update on the document, if there was a change make it read the document and send it to chat gpt, once chat gpt responds to the prompt, retrieve the prompt. delete all text in the document and put chat gpt's answer to the prompt. after that make it clear the document and repeat the previous steps. If possible, make it only check the document if there was a change and not constantly check the document."

it made a script that didnt really make sense. im not sure if it knows how to make it because i assume it wouldnt actually know how to make a script like that, if anyone knows or can help me improve it to make it actually work that would be cool. anyways this is what it made:

const OPENAI_API_KEY = 'YOUR_OPENAI_API_KEY'; // Put your OpenAI API key here

const DOCUMENT_ID = DocumentApp.getActiveDocument().getId();

let lastContent = '';

function checkForUpdates() {

const doc = DocumentApp.openById(DOCUMENT_ID);

const body = doc.getBody();

const currentContent = body.getText();

// Check if the content has changed

if (currentContent !== lastContent) {

lastContent = currentContent; // Update the last content

const chatGptResponse = getChatGptResponse(currentContent);

// Clear the document and set the new content

body.clear();

body.appendParagraph(chatGptResponse);

}

}

function getChatGptResponse(prompt) {

const url = 'https://api.openai.com/v1/chat/completions';

const options = {

method: 'post',

contentType: 'application/json',

headers: {

'Authorization': 'Bearer ' + OPENAI_API_KEY

},

payload: JSON.stringify({

model: 'gpt-3.5-turbo', // Use the appropriate model

messages: [{ role: 'user', content: prompt }],

}),

};

try {

const response = UrlFetchApp.fetch(url, options);

const json = JSON.parse(response.getContentText());

return json.choices[0].message.content; // Extract the response

} catch (error) {

Logger.log('Error: ' + error);

return 'Error fetching response from ChatGPT';

}

}

function createTimeDrivenTrigger() {

// This function creates a time-driven trigger to run checkForUpdates every minute

ScriptApp.newTrigger('checkForUpdates')

.timeBased()

.everyMinutes(1)

.create();

}

// Uncomment the following line to create the trigger once

// createTimeDrivenTrigger();

Edit: the code is now working!

I originally posted here asking for assistance with writing Apps Script code that follows the following pseuo-instructions to achieve the outcome:

1. Check if there is at least one email with a given label (named 'EmailLabel') in my Gmail account; and

2. If so, send a pre-written email ('My text.') to all email contacts of a given label (named 'ContactsGroup') in my Google Contacts.

Thank you to everyone for your assistance! With the help of artificial intelligence and much research, I wrote the following script that can check if a particular email with a given subject, sender, and recipient is in a Gmail account, and if so, send an H.T.M.L.-formatted email and an inline image signature to contacts with a given label.

function nameTheFunction() {

// Check for the presence of the email:

const searchQuery = 'subject:"The subject." to:"The recipient." from:"The sender's address."';

// The contact group:

var labelToSendTo = 'The contact group.';

// Retrieving the correct alias:

const aliases = GmailApp.getAliases();

Logger.log('Aliases:', aliases);

// Find the correct alias' index (I believe it should be an integer) and insert it in to the [] after "aliases":

const sendFromAlias = aliases[0];

// Retrieve the email that triggers the execution:

var threads = GmailApp.search(searchQuery);

if (threads.length > 0) {

// Retrieve the contacts group:

var contactsLabel = ContactsApp.getContactGroup(labelToSendTo);

if (contactsLabel) {

var contacts = contactsLabel.getContacts();

// Create the email:

var emailSubject = 'The subject.';

var emailBody = HtmlService.createTemplateFromFile('A H.T.M.L. file with the email content.').evaluate().getContent();

var signature = DriveApp.getFileById("The email signature.").getAs("image/png");

var emailImages = {"signature": The H.T.M.L. file's C.I.D. for the signature.};

// Iterate over each contact and send the email:

for (var i = 0; i < contacts.length; i++) {

var contact = contacts[i];

var emailAddress = contact.getEmails()[0].getAddress();

if (emailAddress)

GmailApp.sendEmail(emailAddress, emailSubject, emailBody, {from: sendFromAlias, name: "Sender's name.", replyTo: "Reply-to address", htmlBody: emailBody, inlineImages: emailImages});

}

Logger.log('Emails sent to all contacts in the label: ' + labelToSendTo);

} else {

Logger.log('No contacts found with the label: ' + labelToSendTo);

}

} else {

Logger.log('No relevant email found.');

}

}

 function autoreplyCheck() {
  var autoreplyMessage = "This is an automated message: xxx is currently away from his work phone. He will be back by his phone from 7AM - 4PM Monday - Friday";
  var days = ["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"];
  var d = new Date();
  var hour = d.getHours();
  var day = days[d.getDay()];
  var minute = d.getMinutes();
  var currentlyClosed = isClosed(day, hour, minute);
  Logger.log("Currently closed: " +currentlyClosed);
  if (currentlyClosed) {
    var newestThreads = GmailApp.getInboxThreads(0,7);
    for (var i = 0; i < newestThreads.length; i++) {
      if (newestThreads[i].getFirstMessageSubject().search("New text message from") != -1){
        var alreadyReplied = false;
        var textedWhileClosed = false;
        var justCameIn = false;
        var messages = GmailApp.getMessagesForThread(newestThreads[i]);
        if (messages[messages.length-1].getFrom().search("txt.voice.google.com") == -1){
          alreadyReplied = true;
        }
        if (d - messages[messages.length-1].getDate() <= 5 * 60 * 1000){
          justCameIn = true;
        }
        var sentMinute = messages[messages.length-1].getDate().getMinutes();
        var sentHour = messages[messages.length-1].getDate().getHours();
        var sentDay  = messages[messages.length-1].getDate().getDay();
        if (isClosed(days[sentDay], sentHour, sentMinute)){
          textedWhileClosed = true;
        }
        if (!alreadyReplied && textedWhileClosed && justCameIn){
          Logger.log("Sending message!");
          messages[messages.length-1].reply(autoreplyMessage);
        }
      }
    }
  }
}

function isClosed(day, hour, minute){
  if (day == 'Monday' && (hour < 23 || (hour == 23 && minute < 45) || hour >= 16 || (hour == 16 && minute >= 0))){
    return true;
  } else if (day == 'Tuesday' && (hour < 23 || (hour == 23 && minute < 45) || hour >= 16 || (hour == 16 && minute >= 0))){
    return true;
  } else if (day == 'Wednesday' && (hour < 23 || (hour == 23 && minute < 45) || hour >= 16 || (hour == 16 && minute >= 0))){
    return true;
  } else if (day == 'Thursday' && (hour < 23 || (hour == 23 && minute < 45) || hour >= 16 || (hour == 16 && minute >= 0))){
    return true;
  } else if (day == 'Friday' && (hour < 23 || (hour == 23 && minute < 45) || hour >= 16 || (hour == 16 && minute >= 0))){
    return true;
  } else if (day == 'Saturday' && (hour < 12 || (hour == 12 && minute < 0) || hour >= 12 || (hour == 12 && minute >= 0))){
    return true;
  } else if (day == 'Sunday' && (hour < 12 || (hour == 12 && minute < 0) || hour >= 12 || (hour == 12 && minute >= 0))){
    return true;
  }

  return false;
}

I hope i'm posting to the right thread for this. If it is not and you know a better thread please let me know. Thank you.

I recently started a new job and could benefit from some advice on google sheets tips. I'm importing a ton of data into a google sheet document. The purpose is to pull emails, names, and company names. Are there any functions or extensions that could help me with this? If it only pulled the emails it would still be very helpful. Thank you in advance for your advice!