New answers tagged google-apps-script
2
votes
How to save temporary variables in Google Sheets formula?
As of March 2023, you can use the LET function:
=LET(x,A1+B1, IF(x<0, 1+x, x))
You can define multiple variables as well:
=LET(x,A1+B1, y,C1+D1, IF(x<0, 1+x, y))
This feels more natural to me ...
- 21
1
vote
Accepted
How do I get this to function on multiple sheets in a workbook?
var actRange = e.source.getRange(issueOpp || toDoList);
That will not throw a syntax error, but it is semantically incorrect, because it will always get the range '4 - To-do list'!A3:F and never the ...
- 13.3k
0
votes
Google app script not doing everything I want
Can someone help me figure out what i did wrong?
The issue is with this line:
if (activeSheet.getActiveCell() === null) {
That will always get false, because you are not retrieving the value in the ...
- 13.3k
0
votes
Custom function to move and delete a row isn't doing anything
The code in the question calls nine SpreadsheetApp methods every time any value in the spreadsheet is edited, before actually getting to its business, and uses six API calls to move the row. All these ...
- 13.3k
0
votes
Custom function to move and delete a row isn't doing anything
Here is a working version of your script that you can review to see how it differs from your approach.
Script
Sample Spreadsheet Link
function onEdit(e) {
const ss = e.source;
const range = e....
- 3,412
2
votes
Accepted
Determine if onEdit() was triggered by inserting or removing a row
row and column information isn't available via onChange()
You can determine the active row and column in an installable "on change" trigger like this:
const ss = SpreadsheetApp.getActive()...
- 13.3k
2
votes
Basic date manipulation in Google script
This is an old post, but still valuable.
You can set the values back to date objects prior to comparing them:
var curDate = Utilities.formatDate(FullDT, "GMT-0400", "MM/dd/yyyy")
...
- 21
0
votes
Accepted
Trigger script to share calender with new user whenever form updates sheet with a new email address
The form submission doesn't trigger the onEdit(e). You need to use an installable trigger and can key off the form submission using onFormSubmit().
See:
onFormSubmit()
Simple Triggers
Installable ...
- 3,412
1
vote
Accepted
How to do autoresizecolumns() in a Google Sheets file when data imported from another Google Sheets file is changed
Use Installable Trigger
Instead of using the simple trigger onOpen(e) bound to Staff Contacts, use an installable trigger in Staff Roster to make the change in Staff Contacts whenever Staff Roster is ...
- 3,412
0
votes
Two custom fnctions triggered by OnEdit both returning errors
Updated Script
function onEdit(e) {
const ss=SpreadsheetApp.getActiveSpreadsheet();
const range=e.range;
const row = range.getRow();
const value=range.getValue();
const sheets=ss.getSheets()...
- 3,412
0
votes
Two custom fnctions triggered by OnEdit both returning errors
The sortStatus(e) function changes the location of rows in the active sheet, which means that values such as e.range.rowStart will not point to the correct cells by the time projectMove(e) gets ...
- 13.3k
0
votes
Why would I be getting the error: Too many simultaneous invocations: Spreadsheets on a simple once a day trigger?
According to https://developers.google.com/apps-script/guides/services/quotas, quotas are per account and per day, so triggers created for other project scripts may be consuming your account quota.
...
- 44.6k
0
votes
Why does my script return a TypeError when comparing getA1Notation to a string?
onEdit(e): Sheet Name & Range
Simple onEdit(e) trigger that verifies the edited range and the sheet name
function onEdit(e) {
if ((e.range.getA1Notation() == "B3") &&
...
- 3,412
0
votes
How to get a numerical value of a date with Google Apps Script?
Simply multiply it by 1
sheet.getRange('A1').getValue() * 1;
- 9
0
votes
How do I repeat a character a specific number of times based on the value of another cell?
If your intent is to return A A A as opposed to AAA
=JOIN("", REPT("A", A1))
or
=TRIM(REPT("A ", A1))
JOIN REPT TRIM
- 3,412
0
votes
onEdit only updates top row after pasting (editing) multiple rows
You should loop through each cell of e.range to achieve your goal. i.e.:
function onEdit(e) { myFunction(e) };
function myFunction(e) {
let dateTime = Utilities.formatDate(new Date(), "EST-8&...
- 111
Top 50 recent answers are included