Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,308
questions
0
votes
1answer
52 views
Is it possible to set a timer on Google Sheets ui.alert()?
So I have a ui.alert that triggers and asks for confirmation before making an edit on a Google Sheet.
function confirmation(){
//Call Spreadsheet alert and give it a variable
var ui = ...
0
votes
1answer
72 views
search/match a date on Google Sheets using Google script and copy a source range to a destination range OFFSET from that date
UPDATE: I FIGURED IT OUT.
I'm struggling with some simple copy/paste scripting in Google Sheets. I'm a complete scripting newbie. Advanced at Excel/Sheets.
I have a "Today" data entry sheet. I ...
0
votes
1answer
159 views
Include filename of source spreadsheet
I have 2 spreadsheets for 2 employees and I combine them into 1.
From employee 1:
From employee 2:
I use importrange to combine the sheets. I also know now how to get the spreadsheet file name by ...
1
vote
1answer
55 views
Writing a macro to create a new tab and name that tab based on a cell value in Google Sheets
I would like to have a Macro that generates a new tab and names that tab based on a cell value.
There is a tab called 'Dashboard'! on which the user enters information in assigned cells. Afterwards, ...
0
votes
1answer
116 views
How to prevent “invoked too many times per second” error [duplicate]
The main requirement is to find travel time with traffic data between two locations or zip-code.
Inputs parameters would be from Location, destination, arrival time(this is between 6AM 8AM), mode of ...
1
vote
1answer
61 views
Synchronizing Cell Changes Across Sheets
I'm trying to connect a pair of cells on different sheets within the same Google Spreadsheet, so that after a new value is entered into one cell, the other cell will update to match that value, going ...
1
vote
1answer
19 views
Fill-down formula to last used row
This function is to copy-down a formula C2424 to the last used row. Instead of copying it down to the last used formula its copying it to the last existent row within the sheet. How can I do it only ...
0
votes
1answer
19 views
Adding checkbox format in column
Trying to add checkbox format to a column using Google Apps Script to this working formula
sales.getRange("I2").setFormula("=IF(COUNTIF(B:B,B2)=COUNTIFS(B:B,B2,E:E,TRUE),TRUE)");
var ...
0
votes
0answers
16 views
How to create trigger automatically when I open my spreadsheet [duplicate]
I am trying to create Time-based trigger automatically when a file is opened, the function works fine when I execute it manually but it doesn't work when i open the file.
function onOpen(e){
var ...
0
votes
1answer
52 views
Target different column
The following Apps Script function copies certain values from a row given from one sheet to another based on a value and also formats the output.
It works perfectly well. It was written by @Tedinoz ...
0
votes
1answer
58 views
Copying a cell's background colour to other multiple cells via script
Update (01/10/2019); code is now:
function copyFormatting() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var fromRangeGJ14 = sheet.getRange("GJ14");
var toRangeGJ14 = sheet....
1
vote
1answer
48 views
Need help clearing specific cells in multiple tabs in Google Sheets Scripts
I'm having trouble editing this code to get it to edit multiple sheets at the same time. It works for whatever is active, but I'd like to disable that and have it work on each tab named after each ...
0
votes
0answers
15 views
Printing a document for all Google Group members, with merged data
I am looking for a way to print a Google Doc template for all members of Google OU, merging name, surname & email address.
Is there some existing functionality I should I write a Google App ...
3
votes
1answer
85 views
How to automatically convert all Microsoft Office file attachments in Gmail to Google Docs?
My Gmail has thousands of emails. Each email will have several attachments as Microsoft Office files (.doc, .docx, .xls, .xlsx, .ppt, .pptx).
Currently, I want to automatically convert all Microsoft ...
0
votes
1answer
55 views
How to give a column a green border if the second row's holds a date = TODAY()?
I'm trying to make a useful schedule sheet to keep track of all of my class assignments:
I want to write a script in the script editor that says,
for(Column A-Z){
if(A2,B2,etc = TODAY()){
...
0
votes
1answer
447 views
Use `setValue` to update a column based on values of two others that match their respective values in reference cells
I am new to Google AppScript and would really appreciate some help here.
I want to use the setValue along with the for loop and if statement in Google App Script to scan multiple columns in Google ...
1
vote
1answer
30 views
Hide RUN message in Google Sheets? [closed]
I have created a script on a Google Sheet. Is there a way to hide the balloon message "SCRIPT IS RUNNING" ?
0
votes
0answers
14 views
Auto-updating column in Google Sheet [duplicate]
Trying to figure out a way to have column next to modified column auto-update to show a timestamp of the last update.
e.g. 1A is modified and in 1B auto-updated date, 2A modified and 2B auto-update ...
1
vote
0answers
164 views
Split rows by comma into multiple rows and divide between splits
I have a Google Sheets spreadsheet (sheet 1) which I use as source for a function to split a row into several rows in a results worksheet (sheet 2).
The split function divides the row when it finds ...
1
vote
2answers
66 views
If a cell is equal to one of the elements of a list then get data from another cell
I have a spreadsheet that gathers data from a Google Form. In this form, we ask the users to choose between the different seating plans.
This is an example of the data gathered in the Response sheet:...
-1
votes
1answer
27 views
How can I make an attendance table that automatically deletes obsolete day and creates a new?
I need a Button that deletes the entry of the first column(A:A) and creates a new one(H:H) and the Date at cell H2 to automatically set to the next Thursday or Sunday.
Currently, I just recorded a ...
0
votes
1answer
34 views
How to copy values that have just been deleted into 'Archive' Sheet?
Setting up so any expired date rows get deleted. But I would Like to Delete and Move to Archive tab (sheet).
Have tried taking other codes and integrating but cannot find the code.
function ...
1
vote
1answer
144 views
Google Sheets - How to count the occurrence of each day of the week if they are not public holidays?
I would like to count the occurrence of each day of the week in a month if they are not national holidays.
In more details, I would like to know how many Mondays, Tuesdays, Wednesdays, Thursdays, and ...
2
votes
1answer
11 views
Touchscreen tap edits drawing object, doesn't run assigned macro
In Google Sheets, I created a drawing object and assigned it a macro. When I use my laptop in "normal" mode using the touchpad (or at my desktop using my mouse) I can execute the macro by clicking on ...
0
votes
1answer
27 views
How to run one of three different Google Script functions on the same day in a specific order?
I have three Google Script functions in a Google Document (we'll call them functionOne, functionTwo and functionThree). What I would like to do is create a new function (we'll call it newFunction) ...
0
votes
1answer
525 views
How to center table text vertically and horizontally in Google Script for a Document?
I am trying to center the text in a Google Document table both horizontally and vertically for all lines using Google Scripts. The code I have is the following
var style = {};
style[DocumentApp....
1
vote
0answers
32 views
Custom Function to Search *another* Sheet for String and Return Value of Next Column [closed]
I'm trying to create a custom formula that can effectively vlookup the value in X cell against a column in another google sheets spreadsheet and return a value in the next column, the same row as the ...
0
votes
2answers
15 views
Auto Sort different ranges
I have a google sheet that I would like to sort different ranges by the 2nd column in each range.
Example:
A B C D E F G
p 7 1 h 9 1
q 6 2 j 3 2
g 9 3 p 7 3
I need the range A:C sorted by B ...
0
votes
1answer
55 views
Changing a cell value based on another cell's color?
In my Google Sheets in column F, I want to include the word "LATER" based on magenta color (#ff00ff) that is in column B starting from row 227, how should I write this script?
I'll really appreciate ...
0
votes
1answer
53 views
Pull unique values from other sheets as new sheets keep getting added
I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...
0
votes
1answer
38 views
How to remove leading spaces from all lines in a Google Doc?
What is an easy way to remove all leading spaces from lines in a Google Doc?
E.g. if copy-pasting a large amount of text that has been indented with spaces, such as
Lorem ipsum dolor sit amet, ...
0
votes
1answer
30 views
How To Create a Globally Accessible Apps Script for Docs and Sheets?
I've been wanting to create a script that is accessible from any newly created Google Doc or Google Sheet. Is there any way to accomplish this? For example, if I wanted to create a script that ...
0
votes
0answers
242 views
Send multiple emails with the help of Google Script while using different attachments
I have a Google Spreadsheet with a list of emails. I want to build a routine that send email automatically to those email addresses. I also want to attach different PDF/xlsx to this email as per the ...
0
votes
0answers
31 views
Auto forward Gmail from original sender
I have created an GSuite App script from an old post on here. This works great and forwards the email to an email Dropbox in my CRM (Workbooks). When done manually the email sends and the CRM ...
0
votes
2answers
57 views
How can I set this up to run two onedit functions in the same sheet?
all
If you put the two together they don't work
What is the right code to work together
Note that the first to arrange automatic
The second is to set the date if you put only one works, but if you ...
0
votes
0answers
209 views
Google Calendar: Remove yourself from an event you were invited to
I've been invited to a calendar event and accepted it. The event is set to repeat indefinitely. I want to end my invitation after a certain set date.
If I change the end date from the event settings,...
1
vote
1answer
13 views
Setting a trigger to send mail when data is added to a row that was empty
I`ve got an external app that sometimes adds data to the last row of a sheet, is there a way to trigger this to send a mail with the content of that row?
0
votes
1answer
400 views
Syncing Google Sheets to Google Calendar
Automatically add a schedule from Google Sheets into Calendar
I followed the instructions in this video:
https://www.youtube.com/watch?v=MOggwSls7xQ
and it works fine. However, the one thing that ...
0
votes
2answers
43 views
Chronological ordering of checkbox in Google Forms
I have a checkbox in my Google Form, which sends the user selections in the form in an ascending order list:
But I want to grab the list in the order of selection, for example if the user clicks in ...
1
vote
2answers
249 views
Issues with Google Script on collecting Form responses and moving to another sheet
I have been trying to piece together some code for a task at work using Google Sheets and I am having some issues with what I can find versus my current knowledge level. I am trying to create a script ...
-1
votes
1answer
50 views
Google Spreadsheet Trigger which will fire when the active selection changes for a given sheet
This would be equivalent to the Excel Worksheet_SelectionChange event.
1
vote
1answer
2k views
Send a Google Forms response to specific email address based on form response
I need to send Google Forms responses to specific clinicians at my practice depending on whether they have been selected on a drop-down menu in the form. At the moment, Google Forms sends an email to ...
1
vote
2answers
396 views
Is there a way to allow users to access Google Sheets with installable triggers without manually signing in?
I have set up a Google Apps Script that validates data that is entered into various Google Sheets files. I am looking for a way to allow users to access those Google Sheets files without making them ...
0
votes
1answer
45 views
Google Side bar & editing form responses [closed]
I have some code which captures the URL of the edit response, so you can click it, and amend the record if needed. It pops out and opens in a new window. I wondered if it was possible to open it ...
2
votes
1answer
107 views
Opening Google Sheets to A Certain Cell or Column
I am trying to make it so when anyone who is in this weekly calendar opens the doc, it locks to the week we are currently in.
Our dates are in row 4, going across the top of the doc and run from June ...
1
vote
1answer
43 views
Offset called JSON into the next column
I am trying to check for emails based on domain names stored in a column in Google Sheets. I have the API call setup and working, but the data gets pasted into the selected cell. I would like to paste ...
1
vote
0answers
17 views
How to search within an Array to a range in Google sheets and setFontColor to blue [closed]
Don't know why this is puzzling me but I want to search for all parts listed within the primeparts array shown below and find them in a table then color the text blue.
So to clarify, using the partial ...
-1
votes
1answer
239 views
Find and Replace (CTRL+H) script on Google Sheets
I'm looking for a "find and replace" script for Google Sheets.
I have phone numbers on column C, which are in a ###-####### format.
I want to make a script that will replace the "-" to nothing.
...
0
votes
1answer
17 views
Is there a way to sort my Google Script project triggers, on the console?
My console currently looks like this, and I expect the number of triggers to increase:
screencap of my GSuite Developer Hub's project triggers console
I name most of my projects in a standard manner,...
0
votes
1answer
65 views
Help needed with script that auto-updates cells in column in Google Spreadsheet showing last modify date
I'm looking for some help with a script for a spreadsheet.
I found a script (see below) that does half the trick for me, but I encounter two difficulties.
What I am trying to accomplish is the ...
