New answers tagged

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 ...
Kevin Chen's user avatar
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 ...
doubleunary's user avatar
  • 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 ...
doubleunary's user avatar
  • 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 ...
doubleunary's user avatar
  • 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....
Blindspots's user avatar
  • 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()...
doubleunary's user avatar
  • 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") ...
Tom's user avatar
  • 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 ...
Blindspots's user avatar
  • 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 ...
Blindspots's user avatar
  • 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()...
Blindspots's user avatar
  • 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 ...
doubleunary's user avatar
  • 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. ...
Rubén's user avatar
  • 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") && ...
Blindspots's user avatar
  • 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;
George's user avatar
  • 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
Blindspots's user avatar
  • 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&...
Majal's user avatar
  • 111

Top 50 recent answers are included