Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,591
questions
0
votes
0answers
14 views
How to add a part in this script to send a Google Sheets tab as Excel as attachment in the email? [closed]
function emailChart() {
var s = SpreadsheetApp.getActive().getSheetByName('Views for Kristel');
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getActiveSheet().getDataRange();
...
2
votes
1answer
57 views
Gmail bulk Emails manipulation
I am trying to deal with 600,000+ emails in inbox, my goal is to either delete or archive emails before 1/1/2021.
Doing a search and select all search results and then delete or archive as most people ...
0
votes
1answer
28 views
How to run a function everyday at 11 PM?
Currently the function runs every day at 11pm but it runs twice. I'm only needing it to run once though.
function functionsToRun ()
{
appendValues();
clearCells();
addZeros();
}
function ...
0
votes
0answers
30 views
Convert input text into duration values with script on Google Sheets
I'm setting up a sheet to help coworkers keep track of their appointments. For now the only way to get those times is to copy-paste the numbers from a different program used to track the info. The ...
0
votes
1answer
12 views
How to use / refer to a named range when defining another named range in a Google Apps Script?
I have the following script in a Google Sheet Apps Script macro. It is meant to identify & delete a range of rows on a sheet. This was created by originally recording a macro & then attempting ...
0
votes
2answers
17 views
Google Sheets - QUERY - SUM() based on empty column
I need to obtain the sum(B) if the column has empty values I want to reflect 0.
I am automating this process for multiple sheets using app-script and need to use QUERY.
Any suggestions?
0
votes
1answer
22 views
Google Sheets: calling MailApp.sendEmail from a time-triggered script fails due to insufficient permissions. How to fix this?
My sheet runs some calculations on a chart of numbers which updates automatically from a time-driven script. Within that script I put at its very end a function which compares two of the resulting ...
-1
votes
1answer
17 views
Script to Automatically Email Every Morning
I have a Google Form which links to a Google Sheet "Form responses 3". Crew have to check in to our job sites each day and I want to write a script to automatically email our supervisors ...
0
votes
0answers
30 views
Google Apps Script "Range not found" error
function _1() {
var spreadsheet = SpreadsheetApp.getActive();
var numSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets().length
var s1 = numSheets-1
var s2 = numSheets-2
spreadsheet....
0
votes
0answers
27 views
How to fix "Limit Exceeded: Email Total Attachments Size" with Google App script?
I've been running this amazing script (provided in full, below) with great success (thanks to user tedinoz!) but have recently been getting an error that reads "Exception: Limit Exceeded: Email ...
0
votes
1answer
55 views
Loop with dates "don't match the method signature for Utilities.formatDate"
My internet research was vain.
Getting the range of dates & comparing them with today's date. After running the script #1 below I receive Exception: The parameters (String,String,String) don't ...
0
votes
1answer
37 views
How to freeze Google Sheet columns based on date
I am using Google Sheets, and my columns are set up for each month (Jan, Feb, March, etc). I have several rows that are pulling data from other tabs in the sheet to create an at a glance master list. ...
0
votes
2answers
53 views
Preventing Google Sheets from refreshing a custom formula when opening the spreadsheet?
I'll begin with the specific question:
Is it possible to stop function-containing cell from autorefreshing on open?
Or perhaps is it possible to apply a function (which itself calls other functions ...
0
votes
1answer
31 views
Google Sheets copy & paste special values script
I use Google Sheets as our main tool to dispatch construction trucks and communicate with our scale clerks the "who and what they will be picking up". I have researched for days on numerous ...
0
votes
1answer
36 views
Extract Data from 2 columns if it matches condition
This is how my main data sheet looks like
And for any item that has customization, for e.g. - ORGANIC, I want to extract/copy just those data, the meal name & total units to another sheet.
And ...
2
votes
2answers
49 views
How to insert timestamp when using either of the Fill Color tool or the Paint Format tool
I have been using the code below to insert a timestamp in column L when a cell fill color in column C is changed to white.
The code is working well, but while it works when I choose the white color ...
0
votes
2answers
77 views
Insert timestamp when cell background color is changed
I have been using this below code which was working perfectly:
function colorChangeCheck(e) {
var sh = SpreadsheetApp.getActive().getActiveSheet();
var color = sh.getActiveRange().getBackground();...
0
votes
0answers
31 views
Locking Google Sheet Cells Monthly
I am using Google Sheets, and my columns are set up for each month (Jan, Feb, March, etc). I have several rows that are pulling data from other tabs in the sheet to create an at a glance master list. ...
0
votes
1answer
18 views
Google sheet: run a script via the share link (without login)
There is a script for my Google spreadsheet so that every change in a cell is indicated by a red color marker. When users with a Google account that I have shared to the spreadsheet make changes, the ...
-1
votes
1answer
31 views
QueryByName() function from another sheet in google spreadsheet
Is it possible to use the QueryByName() function using another sheet from the same spreadsheet?
=query(nor9609s!A1:Z500,querybyname(A1:Z1,"SELECT `Items_shopid` WHERE `Items_id`>0"))
I ...
0
votes
1answer
22 views
Is it possible to write a script that automatically triggers some function within a Google Sheets add-on?
I'm using some Google Sheets add-on which pulls information from API databases, and it has both a free subscription and paid subscriptions which add many additional abilities which I don't really need,...
0
votes
1answer
29 views
Getting "Exceeded maximum execution time (line 0)" error with custom code when data input increased in "History" sheet
I have a google sheet wherein there are 2 sheets named "Position" and "History" respectively.
I have a custom function that takes data in the "History" sheet and arrange ...
0
votes
2answers
21 views
Assign a default name if the cell value is null
I have a booking form, shown.
I also have a template form, hidden.
This script is assigned to a button to create completed bookings by copying the booking form data to a copy of the template and again ...
0
votes
1answer
15 views
How to enable macros
Looking for some insight. In Google Sheets I added a series of buttons linked to macros. Works just fine for me and another user. Other users are telling me that they click the button and nothing ...
0
votes
1answer
31 views
Update a sheet row by matching a value from another sheet
I'm new to App Script and I'm trying to update an existing row by matching value coming from another sheet. Basically record all the data from the "receipt" to the "record" by ...
0
votes
0answers
11 views
Creating a Button that allows you to Print a select area of cells
I have a document here that I needed some help on!
https://docs.google.com/spreadsheets/d/1AFcHk-5JxMxj1ma-vTmCHIwBVn81Mgmxl001k__IMT4/edit#gid=0
I'd like to create multiple buttons in one tab that, ...
0
votes
2answers
26 views
How do I add an Apps Script function to a Google Sheets cell?
I'm trying to figure out how to count the amount of times a group has clicked on a specific cell containing a cell.
I've gotten it to work with having a separate drawing next to it with the function ...
0
votes
1answer
16 views
End of day data with trigger in google sheets
I want to copy/paste the values of a specific column into the next column using a trigger at a certain time of the day. The script works when I run it manually, it runs as per 'last run' via trigger ...
0
votes
1answer
10 views
Google Sheets. I'm trying to insert a row of CheckBoxes IF B2 Cell has text [closed]
The trick part is... i can't figure out a way to do it, the formulas don't let you add checkboxes. I tried using the code from Google Script to insert a Checkbox but nonetheless i get nowhere. Please, ...
0
votes
0answers
128 views
MMS/SMS Autoreply using Google Voice/Gmail Apps sends reply in different SMS thread
I am using Google Voice, Gmail and Google App Script to implement an 'SMS autoreply' system using the code described here:
How do I auto-reply to text messages received through Google Voice?
Very cool....
0
votes
1answer
33 views
Google Sheets getRangeByName isn't working after duplicating sheet
I use the following function to duplicate a sheet, and it works great.
function duplicateDataSheet() {
const date = getTodayDateAsString();
const newDataName = `${date}_data`;
const ...
0
votes
0answers
11 views
Adding the contents of another cell then clearing it [duplicate]
I'm trying to get a cell from any row in column J to update the value of the E cell in the same row but cant quite figure it out. How i want it to work is someone can come along and add a quantity i....
0
votes
1answer
26 views
Keep copy of all data when the data updates periodically
My sheet gets updated automatically every five minutes. How can I save the previous data, i.e., data just before each update, so that I can keep all data. I also want to make a line graph which ...
0
votes
1answer
25 views
How can I link/stick a range from Sheet1 and a column from a Sheet2?
I have a spreadsheet test here.
My project is a bit long (1000+ rows) but I created something that works in a similar way.
My hypothetical situation:
I receive grades from my students via Google Forms ...
0
votes
1answer
13 views
Google Sheets - Preserving Data after Importing Range
I work in an organization where we give people jobs across 3 of our programs. I am trying to create a spreadsheet that can have a master list of all of our participants and which program they are in (...
0
votes
1answer
26 views
How to return the next month from current month?
I'm trying to return the nextMonth(), but currently both of the following functions I've made don't work.
//this one returns a string of "October1" instead of incrementing by 1
function ...
0
votes
1answer
36 views
How to append a cell value to a certain column ("D2:D")?
Currently the function only allows me to get a range and append it to the "Data" sheet. But I have no way of appending that value from "Sheet3" to another column for instance "...
0
votes
1answer
28 views
How can I insert multiple empty cells, if a certain criteria is met?
I'm currently stuck trying to figure this out. Basically, I'm trying to insert an amount of empty cells only when cells from A2:B2 return a value. I got it to work where if both cells are empty one ...
0
votes
2answers
25 views
Two scripts running simultaneously in the same spreadsheet disturbs each other?
Script 1:
Scraping data from a website and puts the new collected data into Column A and Column B.
function scraping() {
var sheet = SpreadsheetApp.getActive().getSheetByName('PageA');
var url = '...
0
votes
1answer
158 views
How to restore an old version in Google Apps Script's new editor?
Google Apps Script's old editor can have old versions of its scripts restored by using Google's standard File=>See version history (standard as in Google Drive).
However, the new editor doesn't ...
0
votes
1answer
46 views
Get list of Google Drive folders into Google Sheet
I am trying to be a little organized, and want a way to get a list of all folders in my Google Drive in Google Sheet.
It should not have the Files, only folders.
I was able to find following code, but ...
0
votes
0answers
19 views
Colour scale for many rows independently once
I found a solution to colour scale many rows independently using the below script that I found in an answer from Oren Pinsky (thank you!), but can't figure out how to add the selected ranges in the ...
0
votes
0answers
18 views
change sharing permissions in google web app code
So I have a bot on google drive that among other things sorts files in folders.
In the partial code below they are looped and placed in PASS or FAIL folders.
The problem I have is that the files get ...
0
votes
0answers
29 views
Fill in one cell if another has had a date selected and move the row when the date is selected and subjected to an onEdit script
I have built a Google Sheet with a lot of data. I have also put in a script to sort data automatically, by date, when new information is input. The new information can relate to past, present or ...
0
votes
1answer
69 views
Script in Google Sheet to automatically run after importing CSV files to replace current sheet
I'm going to admit I'm a newbie at scripting. I've managed to write the script that I want but I want it to automatically run after importing the CSV file onto Google Sheets.
I have tried a simple ...
0
votes
1answer
32 views
Google form save responses in multiple files based on condition on submit
I have this problem:
A Google Form that saves data into one file (spreadsheet)
Multiple users (admins) that access this file (all of them can see all rows)
Let's say first column is called Department ...
0
votes
0answers
31 views
How can I make this custom formula work with multiple columns?
I came across this script that is a custom formula:
function result(range) {
var splitCol = 1; // split on column B
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var s =...
0
votes
0answers
21 views
Automatically sort 1 sheet on edit
I have a google spreadsheet with multiple sheets.
On one of the sheets, we use Zapier to populate new rows of data when a meeting is scheduled with our team. I'd like the sheet "Meetings" ...
0
votes
1answer
18 views
Replacing part of a formula using Script
So I usually use template formulas (like - =IMPORTRANGE(LINK,Sheet1!A1) where link is the variable I replace using find and replace).
So, now I wanted to automate this task, i.e. for example the ...
0
votes
1answer
74 views
How to speedup Google Sheets script?
I have a script adapted to my needs which was created following this.
I think my script is getting slower over time after adding more entry point in my sheet form.
// Function to submit the data to ...
