Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,809
questions
1
vote
1
answer
1k
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
1
answer
51
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
1
answer
42
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
1
answer
185
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 ...
2
votes
1
answer
859
views
How can I get my set values and formulas to populate to a row of values received from a Google Form?
When I receive submissions from a form I am trying to find a way to include my formulas and set values in the same row as the information submitted by form. My form is only collecting 14 values but my ...
-1
votes
1
answer
82
views
Description based on each checkbox "checked" to populate into a summary on the 2nd tab (sheet)
I am building a spreadsheet that has to tab (sheets), the 1st I have an "assessment checklist" with about 250-30 checkboxes based on what is found. Example (Checkbox is in "A" column and description ...
1
vote
2
answers
2k
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
1
answer
138
views
Format a Cell with Script
I need to write a script to see if a cells value is found in an array, and if it is change the background color of that cell. Can't do this with "Conditional Formatting" as it needs to compare with ...
-1
votes
1
answer
826
views
Writing a spreadsheet script
The above link is a picture of the Google Docs spreadsheet that I am working on.
Basically it is a sale ticket for a small appliance parts store.
I need to be able to select a range of rows and have ...
2
votes
1
answer
1k
views
Google Script Replace Function Reference
Is it possible to build a Google Script where I can select a cell, take its function, replace the cell references (instead of C2,C3,C4, etc. make it AC1,AC2,AC3, etc.), and then return the output of ...
3
votes
2
answers
5k
views
onOpen doesn't execute
I've got a google spreadsheet I created yesterday and used Tools -> Script Editor to create a function in a single file called Code.gs
function onOpen(e) {
var email = (Session.getEffectiveUser()....
1
vote
1
answer
113
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 ...
-1
votes
1
answer
33
views
Which event can I use to detect remote form submission into sheet?
I'm using the WordPress Contact Form 7 plugin as a website contact form. The user's date is submitted to a google spreadsheet and stored as a new row.
Which event can detect that that information was ...
1
vote
1
answer
8k
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 ...
3
votes
1
answer
3k
views
Is it possible to make a summary sheet in Google Docs based on tags?
I have a document in Google Docs. It is very long and I want to tag and extract only certain paragraphs into a second document that is a summary of the first document. I want to be able to dynamically ...
-1
votes
1
answer
43
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 ...
1
vote
1
answer
3k
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 ...
1
vote
1
answer
422
views
Is it possible to calculate in the background of a data validation cell?
Is it possible to calculate in the background of a data validation cell?
I am creating a quite comprehensive spreadsheet that shall help me calculate dice pools in an RPG, but to not trouble you with ...
1
vote
0
answers
367
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
votes
1
answer
177
views
How to navigate to a specific sheet in Google Sheets based upon a cell value
What I am hoping to do is take the =Now() date that is located on my default sheet and in the Row 1 Column 1 cell and somehow when the sheet is accessed onOpen() have it automatically change the ...
1
vote
2
answers
178
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:...
0
votes
1
answer
88
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
1
answer
61
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 ...
0
votes
1
answer
4k
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....
0
votes
1
answer
2k
views
Splitting the contents of a comma separated cell into new rows [duplicate]
Based on: https://stackoverflow.com/questions/52376162/combining-column-data-in-google-data-studio
I have a long list of data, about 200 rows, and each row contains multiple columns of data and a ...
-1
votes
1
answer
328
views
getEditResponseUrls - hyperlinks from second linked form not inserting
I am new to scripts, but I was able to find a one that uses getEditResponseUrls to insert the edit link from a form into a Google Sheet. I've now been asked to add a second sheet to my spreadsheet ...
2
votes
2
answers
87k
views
How do I get a substring from a cell in Google Apps Script?
How do I get a substring from a cell in Google Apps Script?
I'm trying to retrieve some data from a website.
I've already got the whole HTML code
and put it into a cell (thou it could be better put ...
2
votes
2
answers
1k
views
Is there a way to print the source code of Google App Scripts?
I am an old fashioned programmer and like to print the source code of Google App Scripts. Is there a way to do that?
0
votes
1
answer
210
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
2
answers
712
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
1
answer
92
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 ...
-1
votes
1
answer
1k
views
Avoiding an authorization request for Google Scripts
I'm in the midst of trying to custom code some script that will automatically hide some sheets from some users in a shared company spreadsheet. Yesterday it seemed to be working fine, but now it seems ...
-1
votes
3
answers
1k
views
How to run multiple OnEdit scripts in the same sheet?
I am using an auto-timestamp script in Google Sheets. Where when you input something in a particular cell, the script will automatically add a timestamp in a particular cell.
But the problem is that I ...
0
votes
1
answer
872
views
How to list total files for every subfolder in Google Drive?
On google drive I have a directory with over 1000 subfolders. I want to list all subfolders with the total amount of files for each subfolder. This is the script I got so far but it doesn't work cause ...
1
vote
0
answers
1k
views
Sync Google Spreadsheet to Google Calendar (remove any duplicates)
I'm trying to sync my Google sheet to my Google Calendar. However, I keep running into errors. I've tried a myriad of scripts, but none seem to work. I got one to work, but every time I ran the script,...
0
votes
0
answers
10k
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
votes
2
answers
202
views
How to archive cell changes in new tab?
I have a Google sheet that I'm collecting some statistics in. There is a particular cell that I'd like to track changes in a separate tab. I'm looking for a function to copy cell if the cell data ...
-1
votes
2
answers
211
views
Empty cell reference when "Loading..."
I want to do a calculation with imported data from an HTML link. The data is imported every minute. There is loading time and this ensures that the cells are empty during loading. Because my formula ...
0
votes
2
answers
697
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
2
answers
1k
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 ...
0
votes
1
answer
1k
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
1
answer
320
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 ...
3
votes
1
answer
3k
views
Register your Apps Script application and retrieve the client ID and secret for oAuth dance
How do I register an Apps Script project and get the Client Secret for oAuth, and set the callback URL?
-1
votes
1
answer
312
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.
0
votes
1
answer
401
views
Why doesn't my onEdit Function behave the same as my onFormSubmit
With the excellent help of this site I was able to get my onFormSubmit working.
However I need the same thing to happen on another sheet only it has to happen after a cell has been assigned one of two ...
0
votes
1
answer
2k
views
App Script to move a row of data from one Google sheet to a different one (different sheet id's) based on a cell entry
I'm currently trying to adjust a script I found that copies the contents of a row from one tab of a google sheet, pastes it into the next descending empty row in a different tab and then deletes the ...
0
votes
1
answer
58
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,...
1
vote
1
answer
310
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
0
answers
63
views
Google sheets script add data in next column
I'm trying to modify a script to create a PageSpeed report that pulls in certain KPI's from Google Pagespeed API. Once the data is received, i want it to place those KPI's next to the URL and so forth....
1
vote
0
answers
20
views
Creating auto-updating indexed links to tab in spreadsheet
This spreadsheet is a lesson planner for teachers. It will be used by a wide variety of teachers with varying tech skills so it needs to be straightforward and not easily messed up.
The first sheet ...