New answers tagged

0

TypeError: Cannot call method "offset" of undefined. at generateId(Code:6)


0

I expect you have already solved this, but still wanted to post my solution for others who might have the same issue. I edited user0's answer to allow for dynamic criteria function onEdit(event){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sh = ss.getSheets()[0]; // 0 == first sheet. var cellRange = SpreadsheetApp.getActiveSpreadsheet()....


0

You can use this script and bind it to a button: function transferCellValueOnButtonPress() { const ss = SpreadsheetApp; const selectedCell = ss.getCurrentCell(); const selectedCellValue = selectedCell.getValue(); // Target cell const targetCell = ss.getActiveSheet().getRange("C2"); if (!(selectedCell.isBlank() || isNaN(...


-1

I know it's been a few years since this thread was commented on but here goes... I am trying to do basically the same thing. I have a Google Sheet where I am sorting Movie Titles A-Z excluding "A/An/The" at the beginning of the titles (no other formulas). I tried using the script above (“With a script, for formula-less sheets”) but it was not taking into ...


2

Use on edit / on change triggers and the corresponding event object. Example function onEdit(e){ var a1Notation = e.range.getA1Notation(); e.source.toast(a1Notation + ' was clicked'); } Related How to trigger a script in Google sheets when a value changes References https://developers.google.com/apps-script/guides/sheets https://developers.google....


0

There are several ways that you could adapt your script to sort a different column for each sheet. For example, you might create a series of IF statements that combine the sort column and the sheet name. Another option, and the one used in this script, is to use the Javascript switch statement (docs). Though an IF statement is still required, the value ...


2

Thanks to Rubén's comment, I looked at the execution transcript, and found this error: [19-11-28 15:11:32:379 PST] Starting execution [19-11-28 15:11:32:498 PST] SlidesApp.getActivePresentation() [0.114 seconds] [19-11-28 15:11:32:570 PST] Execution failed: Authorization is required to perform that action. (line 6, file "progress") [0 seconds total runtime] ...


1

onEdit / onChange triggers aren't triggered by formula result changes, they are only triggered when a user edits the spreadsheet by using the Google Sheets UI. The solution is instead of monitoring for formula results changes look for the cells being edited by the user whom will make the formula results change.


1

Bypass it's not possible, you could use filter views, create links to jump directly the corresponding row You could create the links before hand You could use Google Apps Script to create a web application that creates the link


0

Convert your custom function to an add-on will not solve the problem because custom functions only can return values. They can't make changes to the spreadsheet, they can't call services that require authorization to be executed. References https://developers.google.com/apps-script/guides/sheets/functions


0

More specifics on Vidar's answer: An anonymous user (accessed via link) can use the script attached to the sheet when they are given edit rights AND they sign-in with a google account, and give the permissions to run. So an anonymous user without a gmail account will not be able to use the scripts. To allow anyone to use the script it has to be published ...


1

Tl;Dr: Yes, it's possible. With Google Apps Script you could read/write Google Sheets data. To effectively use it you should know "some" programming, "some" JavaScript and apply "some" "spreadsheet thinking". By "spreadsheet thinking" I mean that you should know how spreadsheets handle values and formulas, and understand how values (single and arrays) are ...


1

Maybe your Google regular/commercial account reached a quota From OAuth API Verification FAQ Users seeing the Unverified App Screen or "Sign-in with Google temporarily disabled" Why are users seeing this? To protect users and Google systems from abuse, unverified apps that are accessing restricted or sensitive scopes have a 100-user quota ...


1

Google Apps Script doesn't support let Related Google Apps Script Javascript compatibility error Reference Basic JavaScript Features


0

I did find a solution to this. In my previous question Is there any way to set different values for multiple ranges all at once?, I found a bug in my own answer. I was able to fix it using the Google Sheets API. As it turns out, this exact function can now also be used to set values and formulas interchangeably without blowing away existing values or ...


0

It's very likely that the problem are destination.getRange('A1:B').activate(); destination.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true}); and spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true); spreadsheet.getRange('A:B').activate(); spreadsheet.getActiveRangeList().clear({contentsOnly: true, ...


0

There is an error on elseif. The correct syntax is else if


1

In a general sense, in Google Apps Script, the most "standardized way" to have a variable that increments on each execution (a persistent variable) that we could use is the Google Apps Script Properties Service Another common way is to save the row number is to save it in a spreadsheet cell.


0

As requested this is one way to do it in google app scripts: function onEdit(e){ var sheetName = "Currency"; //name of sheet we want to adjust formatting var currencyCol = 1; //column A var amountCol = 2; //column B var defaultFormat = "[$$]#,##0.00"; var currencyFormat = {"USD":"[$$]#,##0.00", "POUND":"[$£]#,##0.00", ...


1

Start Date Number of Days 3/1/2019 2 Assuming 3/1/2019 is cell A2 use the following formula =arrayformula(sequence(B2+1,1,0)+A2) and the result will be: 3/1/2019 3/2/2019 3/3/2019 If you want to do weeks a simple modification like this will give you the exact result you asked for: =arrayformula(sequence(B2+1,1,0,7)+A2) SEQUENCE


0

I created a sheet called colors and with the code below it will automatically change the background color from #ff00ff format. I included comments for you to understand, but it should be strait forward. function onEdit(e) { r = e.range; if(r.getSheet().getSheetName() == "colors"){ //the sheet I want to apply this to is called colors var rows = r....


1

Google Apps Script can't redirect to another website because it only acts on the back-end not on the front-end. Related This site Show quiz result on the same page in Google Forms Stack Overflow Old Google Form redirect after submission (This doesn't work with the current version of Google Forms) Google Form App Script Automatic URL Redirect


1

The following isn't an answer to your question per se, but it is an exploration of the syntax surrounding TextStyle and RichTextValue. It may assist in understanding why var x = A1.getRuns(); doesn't work, and how you might build code to analyse cells. Note the programmatic creation of RichText is another thing altogether. Spreadsheet Screenshot function ...


Top 50 recent answers are included