Questions tagged [google-apps-script]
For questions about using Google Apps Script to extend Google applications
1,791
questions
0
votes
0
answers
11
views
How to change the message display when sending the code to the mail [closed]
// Display the HTML form when the web app is accessed
function doGet() {
var template = HtmlService.createTemplateFromFile('form');
return template.evaluate();
}
// Handle the form submission ...
0
votes
0
answers
9
views
Run Google sheets script on multiple tabs, affecting different rows on each tab [migrated]
I am trying to run my script across multiple tabs on my Google sheet, to affect a range of different rows on each (multiple drop down functions)
How do I get it to affect specific sheets, and not them ...
- 1
1
vote
1
answer
22
views
How do I get this to function on multiple sheets in a workbook?
I'm still fairly new to Apps Script. I found this answer several months ago and it's been working great, but I've been asked to implement it on several sheets in a workbook instead of just the initial ...
-1
votes
1
answer
10
views
Google app script not doing everything I want
The script i wrote is below. However it is putting the data on sheet tracking and moving down to row 21 instead of 2 and then keeps overwriting 21 instead of moving to 3 then 4. Can someone help me ...
0
votes
2
answers
64
views
Custom function to move and delete a row isn't doing anything
I have a script bound to my Google spreadsheet with a simple onEdit(e) trigger that isn't working.
The script is supposed to move the edited row to Closed Cases, then delete the original row, if the ...
- 1
0
votes
1
answer
22
views
Determine if onEdit() was triggered by inserting or removing a row
I have two spreadsheets, SS1 and SS2.
I need to update SS2 when a row is inserted into or removed from SS1, or when a certain range of cells is changed in SS1.
I use an installed onChange() trigger to ...
- 125
1
vote
1
answer
17
views
Trigger script to share calender with new user whenever form updates sheet with a new email address
I have a Google form that allows people to submit their name and email address in order to sign up for a private shared calendar.
The form populates a Google sheet.
I want to automatically add new ...
- 13
2
votes
1
answer
37
views
How to do autoresizecolumns() in a Google Sheets file when data imported from another Google Sheets file is changed
I'm new to Google Sheets and apps scripts. This is my first ever question. I apologize in advance if I don't get it quite right.
Google Sheets file Staff Roster sheet Roster contains names, contact ...
- 125
1
vote
0
answers
20
views
Insert a new row in Sheet 2 for each cell in a range in Sheet 1 that is not empty [closed]
Hi I'm new to the Google Sheets App Script and not sure where to start. I'm trying to write a Script that will insert a new row in Sheet 2 for each cell in a range in Sheet 1 that is not empty.
For ...
- 11
0
votes
2
answers
68
views
Two custom fnctions triggered by OnEdit both returning errors
I have two custom functions bound to a Google spreadsheet with an onEdit() simple trigger that are returning errors.
The first function sortStatus() sorts the columns in range A2:R by I2:I (col 9) ...
- 1
1
vote
1
answer
43
views
Format a numbered list in google docs into a table template?
I have a list with a url, an emoji (of a set of 3), and a name in a numbered list.
Taking a list like this:
https://news.ycombinator.com/item?id=38122061 🥾 D. onohoe
https://news.ycombinator.com/...
- 11
0
votes
1
answer
35
views
Timestamp on change of another range
I have a Planning sheet that I use to follow budgets that are reported every week.
I'd like to have K7:18 stamped with a date when there is a change in the corresponding cell of I7:I18 range.
I cannot ...
0
votes
0
answers
21
views
Putting a custom function in same file as Apps Scripts [duplicate]
I'm sure I'm missing something very simple, but here is my problem. I have a spreadsheet with an apps script that has global variables that require authorization (due to openBy). I want to add a ...
0
votes
1
answer
123
views
I am using Google App Scripts SpreadSheet.openById(), but it assumes Sheet0 as active sheet?
I am trying to create a trigger to get id when there is a change in sheet selection which is working fine when each google sheet has its own google app script, but I want to create a google app script ...
- 11
1
vote
1
answer
104
views
SpreadsheetApp.openById gives unauthorised error
I am trying to trigger a function in Google Apps Scripts, and am using openById("Sheet_Id);
But on using it, I am getting this error:
Exception: You do not have permission to call SpreadsheetApp....
- 11
-1
votes
2
answers
42
views
Google Apps Script for gradebook sharing: Create duplicates of current sheet, one per row of data, with importrange of the source and header rows
The goal is to create a workaround to the typical problem of not having per-row privileges. I want students to only see their row of the grading document. Typical solution is to share another sheet ...
1
vote
1
answer
44
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 ...
- 11
0
votes
1
answer
23
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
21
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
1
answer
159
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 ...
- 257
1
vote
0
answers
24
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
11
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,468
0
votes
1
answer
22
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
13
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
68
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
66
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
65
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
1
vote
1
answer
330
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 ...
- 13
0
votes
2
answers
270
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
52
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 ...
-1
votes
2
answers
129
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
0
votes
1
answer
99
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
34
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
190
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
646
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
91
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,724
0
votes
1
answer
60
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
72
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
168
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()...
0
votes
1
answer
38
views
Remove protection from a specific cell when contents are deleted
I am trying to remove the protection from a specific cell after its content is deleted and then change its background to white.
Here is the script:
function onEdit(e) {
var spreadsheet = ...
1
vote
1
answer
190
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
1
vote
1
answer
265
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.
...
- 11
1
vote
1
answer
125
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
159
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
55
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
1
answer
434
views
Buttons don't work on iPad with Google Sheets - the onEdit(e) workaround isn't working. Suggestions?
I have a button that works with the function Yeah_Go(), but only when using it on a browser on a laptop. When I try to press the button on an iPad Google Sheets mobile app, it doesn't work. Instead, ...
- 103
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
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