Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,793
questions
1
vote
1
answer
2k
views
Cannot find active sheet: TypeError: read properties of undefined (reading 'source')
I'm trying to add the current date to a cell in column K when any cell in that respective row is changed.
I found this script and altered it for my situation so I can deploy it as a trigger:
function ...
- 449
0
votes
1
answer
28
views
Insert formula, process and replace by raw value
What is the best approach to achieve the following:
If a cell in column A has content, then place a formula into the cell in column B in the same row, referencing the cell in column A.
Once the ...
- 5,468
-1
votes
1
answer
151
views
App Script code is so slow [closed]
I have created a script for my sheet to do the following when a certain cell is edited:
Get the timestamp
Get the user E-mail
Add the text "New" in another spreadsheet.
function ...
- 25
-1
votes
1
answer
137
views
How can I get the same format of cells in my script?
I've made a script, but I need the numbers off 3 rows to be on percentage and I also want to use only 2 decimals and format the date (one of my table colums) as "yyyy-MM-dd".
this is my ...
- 1
1
vote
1
answer
81
views
Custom conditional in Google Sheets: How to integrate in Apps Script?
My goal is to have Google Sheets detect when a cell's contents has a strikethrough added, and to change the font color of that cell in response. I learned that this is not possible without an Apps ...
0
votes
1
answer
49
views
Run two scripts simultaneously on one google sheet
Good morning,
I'm very new to scripting and I have two scripts on my file that work fine independently (either one or the other) but I will need both scripts to run at the same time at all times . ...
2
votes
0
answers
88
views
Is it possible to make clickable links using Logger.log?
Is it possible to make clickable links using Logger.log?
I have this code that log url Logger.log(url); that logs text in console.
I wonder if this is possible to make that url clickable by using ...
- 179
0
votes
2
answers
97
views
Clear dropdown when the value in another cell is modified
I am trying to figure out why my script is not working.
I have a sheet called "active unentered invoices" where my master data is housed. I have a true/false statement in column V and ...
0
votes
1
answer
231
views
How to copy an existing google form filled survey (with answers) to a new google form survey (to create a different link than the original survey)?
I am using google forms to survey the same patient each month.
Is there a way for me to create a different link to the patient's previous survey (and maintaining his/her answers) or to quickly copy ...
- 121
0
votes
1
answer
2k
views
Writing an Array from Apps Script to Google Sheets
I have been out of coding for a while and have forgotten much. I am attempting to read data from a selection of cells from one page and then write it to another section if it meets the criteria. Both ...
0
votes
1
answer
31
views
I can not remove data validation with 2 criterias - Function onEdit(e)
I would like to run function onEdit(e) to remove Data validation with these 2 criterias as below:
Remove for columns G,H,I,K if L is "no need".
Remove for columns M,N,O,Q if R is "no ...
- 1
1
vote
0
answers
43
views
Using an android add (Http Shortcut) to start a google app script [closed]
I am totally a noob to coding and have no background in programming, hence have tried my best to understand things but am unable to.
I am trying to get use an android app (HTTP Shortcut) to do a POST ...
- 11
0
votes
1
answer
40
views
Add Timestamp to Update Tab onEdit of Main Tab
I would like to have it that when a row is edited that it only copies the name, phone and adds a timestamp of the edit on another tab in Google Sheets. Each row on Update sheet should be a new row ...
0
votes
1
answer
982
views
OnEdit Not firing
[This Script]1 seems to be exactly what I want. Yet, it doesn't "Fire" when the reference cell is edited. That is, the target cell is not cleared. Just nothing happens upon editing the ...
0
votes
0
answers
184
views
How do you import cell fill color from another spreadsheet? [duplicate]
I am currently working on a spreadsheet with 100+ conditional formatting rules on columns, and the formatting rules significantly reduce initial page load speed. I already implemented IMPORTRANGE(), ...
0
votes
1
answer
34
views
Pulling all data from a column
tl:dr - I have a code that can work but want to make it better by pulling everything from column E into 1 message (including header)
I am trying to get the output of a singular column to post to ...
0
votes
1
answer
77
views
Script to automatically change the active sheet after user completes populating a range of cells
i am looking for solution to merge text fragements in Apps script to automate a process.
I have 3 sheets (sheet_1, sheet_2,sheet_3). In each of the sheets i have 10 cells (A1:A10) to be filled by the ...
2
votes
2
answers
163
views
Creating a custom menu in google sheets
I'm currently using the below code to create a custom menu called 'Team Admin' in Google sheets which allows a user to activate the function 'newSheet' when the menu item 'Add Agents' is clicked.
...
1
vote
1
answer
612
views
How to integrate the real OpenAI ChatGPT with Google Sheets?
Nowadays there are a lot of posts using the word ChatGPT but many of them use it as a common noun to refer to GPT-3 and some chatbots, not to specically make refence to https://chat.openai.com/chat / ...
- 44.6k
1
vote
1
answer
314
views
Automatically record daily values from a column in a new row
I currently have a project tracker in a Google sheet which records a project name, status character column and three progress columns as below:
I would like to record a daily log of each progress ...
- 15
0
votes
1
answer
40
views
IMPORTRANGE to refer the function but not the value/result
There are 5 different spreadsheets, i.e. Master, 1, 2, 3, 4)
All cell A1 in 5 different spreadsheets have the same function e.g. =count(B1:B5), they count the data in their own spreadsheet.
And I want ...
- 1
0
votes
1
answer
74
views
Copying Google sheets data to newly inserted rows (which have been inserted by a script)
We would like to extend an (amazing) script for Google Sheets that inserts multiple new rows in Google Sheets based on a cell value (original question and answer: https://webapps.stackexchange.com/a/...
- 15
0
votes
1
answer
32
views
Slow Script when duplicating tabs Google Sheets
I'm currently using the below script which essentially pulls a list of agents from a list then creates new tabs using a template sheet as the basis for the new ones and renames them all as per the ...
1
vote
1
answer
835
views
Adding multiple new rows in Google Sheets based on a cell value
I need to try and find a formula or script that can insert new rows in a Google Sheet between other rows of data, based on cell values. In the below example, cell X2 is showing "3", so we ...
- 15
0
votes
0
answers
304
views
Data Validation using Named range from Apps Script
In Dec22 Google changed how data validation is maintained [Release note].1
I have thousands of cells using different ranges for validation, and it's now impossible to maintain. I want to change them ...
0
votes
1
answer
114
views
Create a countdown timer from 2 different date and time
I have created a google sheet which consist of dates & time. What I want to do is to create a countdown timer from those 2 data.
Here's my spreadsheet setup.
Column A:1 11/01/2023 15:10:00 PM
...
- 3
0
votes
0
answers
36
views
Onedit non contiguous range problem
I have an OnEdit function, when it is trigger I need to make some operations on the modified cells.
Currently my code is going well when I have one modified cell or a contiguous range of modified ...
0
votes
2
answers
280
views
APP Script list Google Drive files in Sheet
I've been on a hunt to find a script that will return file names and hyperlinks into a Google Sheet. I found the following script that works perfectly, but is limited to PDFs. How do I modify this ...
- 3
0
votes
1
answer
31
views
How to automatically update one google sheet based on the data available in other sheet
I have two different google sheets; one with the leads data and the other with unsubscribe data. Is there a way to automatically update the lead's data whenever a new unsubscribe email is added to the ...
0
votes
2
answers
367
views
How to create pop up and add link to a cell
I have a sheets file that requires users to inserts links in the cells of a specific column when they add the information required.
However, to make sure this step is done accordingly, I would like to ...
- 15
1
vote
1
answer
105
views
How to set up a submit button from sheet 1 to remove the protection on sheet 2 so a new user can input data, and then it will lock back up again [closed]
I have 2 sheets (Sheet 1 & Sheet 2). Sheet 1 is protected besides a few fillable cells, including an apps script button to log the entered data to Sheet 2. Sheet 2 is protected and can not be ...
- 11
0
votes
1
answer
104
views
When my function is called from onEdit it does not perform the same as calling directly
Per code below, a direct call to getWeather in the editor executes successfully to completion. But when triggered via onEdit in the spreadsheet, it properly displays my debug line "In ...
- 3
0
votes
2
answers
761
views
Blinking cells in Google Sheets
I am trying to get a cell to blink when a file is opened. I currently have the following code that I have found from here, but don't know how to modify it to blink when the file is open. Can anyone ...
1
vote
1
answer
44
views
Stop running script on last row with content
I have a script that sends emails to a list of recipients per row.
For every row it checks certain conditions and sends an email with different information taken from different columns of that row.
...
- 15
3
votes
1
answer
693
views
How to parse RFC3339 with TZ to field so that Google Sheets recognizes it as valid datetime stamp
I have a Google Apps Script which receives a date/time value formatted in rfc3339, with Timezone info, for example:
2022-12-29T06:34:07.442788Z
However, Google Sheets doesn't recognize this as a date (...
- 33
0
votes
1
answer
27
views
Is there a function where if you modify a certain cell, the contents of another cell in the same column will be cleared?
E.g. If the range of cells is A1:A20 and the independent variable is A5.
If A5 is modified then the content of A1 to A20 except for A5 will be cleared.
- 9
0
votes
1
answer
81
views
Automatically create new Sheets like 1st 2nd 3rd 4th and so on
I am using google Sheets
I want to do the following but I cant figure out how:
We do have like a daily Report sheet. We need it for every day, so I create 31 new Sheettabs 1 for each day and give the ...
- 9
2
votes
1
answer
59
views
automatically generate new dataset from two data sheets // merge google sheets
I have two data sets:
first data set: list of clients who bought a sertain product package:
second data set: list of products for every product package:
what I need is to generate (automatically) a ...
- 35
2
votes
1
answer
48
views
Exempting a cell containing a specific character
First, let me clarify that this is my first time doing anything with Google Apps Script or javascript in general. I'm trying to create a function that adds checkboxes to all cells in a column which is ...
- 23
0
votes
1
answer
744
views
Defining variable type for google sheets script
I'm building an arduino project to measure environmental conditions in a salami conditioning cabinet. I've "stolen" some code from another project.
https://iotdesignpro.com/articles/esp32-...
- 1
1
vote
1
answer
26
views
How do I add multiple cellvalue in a IF function?
I'm trying to add more values with "Select a name" to the script so when any of these values pop up it'll clearcontent
function onEdit(e) {
let range=e.range;
let activeRow = range....
- 15
0
votes
1
answer
68
views
How do I Apply a simple script to a specific tab in a Google Sheet? I know zero code
I'm trying to apply this script to a specific tab to not ruin all the other tabs.
function onEdit(e) {
let range=e.range;
let activeRow = range.getRow();
let activeColumn = range.getColumn();
...
- 15
-1
votes
1
answer
64
views
Borders in conditonal formatting
I am looking to remove borders in cells AG64:BE68 on the below spreadsheet based on whether Cell AG64 is blank or populated. I am aware its not possible to do just using conditonal formatting but ...
1
vote
1
answer
2k
views
service spreadsheets timed out while accessing document with id, after copy some data between spreadsheets
I need to copy data from one spreadsheet to another every day.
There is 13 columns and about 50k rows (counts of rows is growing with time).
But from time to time, after copy some data, function is ...
0
votes
1
answer
1k
views
Apps script wont let me use (row, column) notation with sheet.getRange
var range = sheet.getRange(i, 1);
It only lets me use
var range = sheet.getRange('A1');
otherwise it gives me error message
Exception: The parameters (String,number) don't match the method signature ...
0
votes
1
answer
288
views
Im trying to compare 2 strings and it's returning true even when false
if (values[i][0] = values[x][0] && x != i )
I and X are both row numbers and 0 just points it to column A. The code under this runs every time. I'm running it on the sheet called "Games ...
-1
votes
3
answers
78
views
Multiple on edit in one gsheet
I'm trying to create a spreadsheet for my team, and using this script I am only getting one of the on edit if statements to function. How can I fix this? I'm not very versed in script writing.
...
1
vote
1
answer
124
views
Google Sheets - Button link to script with a range input
Is there a way to write a script, assign it to a button, and press the button to execute the script AFTER selection a cell range that serves as an input parameter to the script in question?
- 11
1
vote
2
answers
482
views
Trying To Change Color Of An Entire Row Based On Found Text In One Google Sheets Cell
I found some very simple Apps Script code online that scans for a set value (MONDAY) in a Google sheets cell and then changes the color of the cell if it finds a match.
I am trying to modify the code ...
1
vote
1
answer
60
views
How can set up a Google Sheet menu via AppsScript to pull all of my emails from gmail? [closed]
Hi I am trying to create a menu on a Google Sheet that lets me pull all of my emails from Gmail. I can't figure out how to do this with the new Google Apps Script vs. the old menu of Tools <- ...
- 11