Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,785
questions
0
votes
1
answer
10
views
Adding (and updating) time stamp when cell color is changed in Google Sheets
I'm very new to scripts so please bear with me. I want to make a condition where if a cell color is changed, a new timestamp is created. So a cell will start from white (unused) and change to ~3 ...
0
votes
1
answer
18
views
Google Sheets complex formula or script is needed
I have trouble with creating a complex conditional formula in Google Sheets. Honestly, I feel there is a need for some script with a "for" cycle, but I'm not sure.
The task is the following. ...
- 103
1
vote
1
answer
16
views
onEdit updates correctly, but then overwrites following columns after pasting (editing) multiple rows
I'm using Google Spreadsheets and found a script here:
onEdit only updates top row after pasting (editing) multiple rows
I manipulated the script to fit my needs, and I need it to only look at column ...
- 13
0
votes
0
answers
13
views
automatically sort data in google sheets [migrated]
I am a total newbie here trying to make my spreadsheet AUTOMATICALLY sort the data based on DATE. I've attached a photo of the spreadsheet I am using.
You'll notice a WORKDAY formula in column G. Once ...
- 1
0
votes
1
answer
33
views
Change cell color if data is changed from "initial value" in Google Sheets?
How can I architect a system where a customer is given a spreadsheet with 1000 rows / 5-columns each of data, and if they edit any cell, it gets highlighted blue? Probably doing this using App scripts ...
- 235
1
vote
0
answers
17
views
How can I get email notifications for changed cells, including what changed and context from some nearby cells? [closed]
I would like to receive help in modifying the scripts in Email notification when any cell in specific column is changed.
Based on previous code, how can I make the email content include not only the ...
- 11
0
votes
0
answers
8
views
Google Sheets: Change text color of cells in other columns if specific cell is focused?
I have a sheet with four columns.
What I would like to do is the following:
If the cursor is inside a cell in column A (meaning that cell is focused), the cells of that row from the other three ...
- 5,438
0
votes
1
answer
21
views
Failure to autsort Google Sheets
I have a Google sheets that has the responses from a Google Form. I want the responses to be sorted according to contents of three columns (9, 12 and 13) when the sheet is opened. I'm using this ...
- 135
1
vote
0
answers
12
views
Update Google slide training suite using scripts [closed]
I have a series of Google slide decks which form a training pathway and we need to update them asap but don't have a lot of time for donkey work.
Our brand colours have recently updated and I would ...
1
vote
1
answer
36
views
How can I Speed up an onEdit script so that it captures all subsequent edits?
I have a bit of script to capture changes made to a table of information, pulling the new value as well as the person's ID, a category, and the reference of the item being changed. Altogether the ...
- 13
2
votes
1
answer
63
views
How do I modify my script to unhide all sheets subject to multiple exceptions? [closed]
How would I modify this script to make multiple exceptions not just for one sheet? I am trying to unhide all sheets except for a few not just for one sheet.
function main(){
activateAllSheetsExcept(...
1
vote
1
answer
46
views
Locate a text string and return the width of the range containing it based on whether it's in a single or merged cell
I have calendar data in my spreadsheet. Each row is a day, and each column is a half-hour increment of time from that day.
When I add events to the calendar I block off the time by merging the ...
- 11
0
votes
1
answer
86
views
Apps Script that automates cell background coloring from HEX/RGB Codes
I'm creating color palettes with Google Sheets as a basis for a color reference book: You can see a sheetproof here. I wanted to batch fill the cells' background in Column C according to the HEX/RGB ...
- 11
-1
votes
2
answers
81
views
Google Apps Script While Loop does not stop when it should
I'm new to Google Apps Script. I am trying to write a while loop for a spreadsheet, but the loop isn't stopping when it should.
The spreadsheet contains a formula that returns randomized Sudoku ...
- 1
0
votes
1
answer
32
views
Google Sheets Date/Time Stamp
I have a Google Sheet with a dropdown menu in column U.I would like column V to timestamp when column U is changed. I've used the following code however this only timestamps the first time that column ...
-2
votes
2
answers
73
views
Google apps script or formula to solve convert to proper case, with exceptions
CLARIFICATION: I asked for a script because there are some other things I'm doing as well and I figured I would combine them all in a single script. But if a formula is the best way to do it, I can ...
- 1
-1
votes
1
answer
58
views
Search an entire Google Sheet for a value and return true/false
I need to search every sheet in a Google sheet for a value - currency - and return true if it's found. The code below is returning false for everything
function searchForNumber(value) {
var sheet = ...
1
vote
0
answers
23
views
How to simulate Google Form's 'Send' button in Google Apps Script?
When sending a Google Form manually via the "Send" button within a form, you would see that the email addresses are recorded in the "Responses" tab, even if they have not responded ...
- 111
0
votes
1
answer
95
views
Clear cells based on checkbox repeated Google Sheets
I have checkboxes in columns A, E, H, L, O, S, V, and Z
I have a script that is working which allows me to check the box in a2 and it deletes data in e2 and f2. I then am able to reverse the script so ...
- 1
1
vote
1
answer
267
views
How to protect a range in Google sheet using a macro from Google Apps script?
I recorded a macro using the traditional way (GUI) to protect a range. The following script was generated.
function protect() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('...
- 121
-1
votes
1
answer
34
views
Run existing macro on a new Google Sheet with least number of clicks
How to execute an existing macro (or any other kind of script) on a new Google Sheet with the least number of clicks/keystrokes?
Or even automatically when opening it?
Context: My bank sends me a new ...
- 8,694
0
votes
1
answer
21
views
Can I use responses of a previous google form in a new google form?
I have a Google form that is collecting the Event date, event name, employee name, and his/her assistance in that event(Yes/No). After collecting those answers, we want to use the data to create ...
- 1
0
votes
1
answer
56
views
App Script for getting data from one cell
I have a large database with unstructured data. In the below pic column A contains the 14 districts of Kerala. Column C:G represents the customer details. If any of the cells from C2 to G2 contain a ...
- 21
0
votes
1
answer
99
views
How can I Convert date from 'Mon Jun 05 2023 00:00:00 GMT+0100 (British Summer Time) to 'DD-MM-YYYY' in google APP Script
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('TRIGGER');
menu.addItem('create new doc' , 'createNewGoogleDocs');
menu.addToUi();
}
function createNewGoogleDocs(...
0
votes
0
answers
6
views
Conditional border line according to different data in google apps script [duplicate]
i'm trying to get different border line like image below.
What i've been trying were below:
I've selected sheet like this.
function myBorder() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()...
1
vote
1
answer
94
views
Trouble Google Script in Sheets to Print from A1 to cell that contains text "stop print". sheet.setPrintArea not a function- anything similar?
I've been working with both ChatGPT and Google Bard trying to learn how to do this.
It seems that sheet.setPrintArea is not a function nor does there seem to be one similar.
I did a search on ...
- 13
0
votes
1
answer
106
views
function no longer working on mobile
I have a function that uses the onEdit trigger. It was working great both on PC and mobile yesterday, but today it no longer works on mobile but still functions on my PC. Not sure what is going on.
...
1
vote
1
answer
65
views
AppendRow Column Offset
I wrote a Google Sheets Apps Script to function as a data entry form for my team to use. The script works great, but I'm looking for a way to improve it. I'd like to be able to skip the first column ...
- 13
1
vote
1
answer
97
views
How do you work with the Color Class array?
In Google Sheets using Apps Script, I want to read in the font color in many cells, change some values (change colors) in the array, and write them back to the cells.
I have previously used ...
- 11
0
votes
1
answer
44
views
How to divide the values of a table in google slides by a number using App Script? [closed]
I have added a table from a google sheet to google slides using app scripts. However, I want to divide the values of the table by 1000000. Is there a way to divide the table values of the table in ...
0
votes
0
answers
10
views
How to open a specific tab based on todays month [duplicate]
I have a google document that i made a tab for each month like this, Aug, Sept, Oct, Nov, Dec, Jan, Feb, Mar, April, and May. I would like to be able to open the the document and automatically open ...
1
vote
1
answer
1k
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
22
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,438
-1
votes
1
answer
116
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
55
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
0
votes
0
answers
57
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
41
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
76
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
67
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
86
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
1k
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
23
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
29
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
31
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
657
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
122
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(), ...
-1
votes
1
answer
28
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
64
views
Google Sheets - merge text in Apps script
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
154
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
556
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 / ...
- 43.9k