New answers tagged google-apps-script
0
The SpreadsheetApp calls are very expensive in terms of execution time, so considering the number of calls that applying the protection implies I don't think that it could be possible to reduce the execution time by doing slight changes.
One alternative is the use the Sheets Advance Service and batch update operations.
Note
Class RangeList doesn't ...
1
There isn't a built-in feature to protect or edit the protection of several sheets at once but Google Apps Script could be used to automated this task or an add-on created by yourself or by someone else.
Reference
Use Add-ons & Apps Script
Extending Google Sheets
Related
Script to add protection and remove the user running it?
0
This QUERY version of the same FILTER function 100% solved the issue thanks to @player0
=QUERY(FORMULARIO!A13:Q, "select C where not upper(J) = 'PAGÓ' and B = 10 and C is not null and A > date '"&TEXT(TODAY()-5, "yyyy-mm-dd")&"' and not Q matches '"&TEXTJOIN("|", 1, QUERY(FORMULARIO!J13:Q, "select Q where upper(J) = 'PAGÓ'", 0))&"'", 0)
0
The IF statement in your onEdit(e) script already tests for row and column. You want to test for sheet name also.
This isn't an uncommon question and yours is close duplicate of Google Script onedit(e) event restricted to a specific sheet and specific range with a good answer by @Tanaike.
In your case, extend your if statement like this:
if(e.range....
0
You want to copy a formula from one sheet to many other sheets in the same spreadsheet.
You are right that a script including getFormula (for which there is relatively little discussion) and setFormula (for which, as @Rubén says, there are many precedents) is on the right track. The following code provides a working example of how you might proceed.
Cell ...
1
It sounds to me like you're trying to use a spreadsheet to do the job of a database. (2000 rows is nothing. But three concurrent users, 13 tabs, feeding three other worksheets .. put those together and you're not in spreadsheet-land any more).
I'd suggest a review of your overall IT and business architecture, and make decisions from there about what ...
1
Redirectnator v1.1 is a spreadsheet created by Jeff Louella. It has a function built-in, with server code (301/302/200/404) and final url. You can make a copy here : https://www.thetechseo.com/seo-tools/redirect-checker/
Here's the script you can use/modify :
function redirectCheck(url, user, pwd) {
try {
function getResp(url, user, pwd){
...
0
enter preformatted text here
function testColor() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
conditionalFormatRules.push(SpreadsheetApp....
3
You can build a custom parser for any fixed JSON string using regular expressions. For example, if you want to extract the child items into columns you can do the following (assuming your JSON string is in A1):
=SPLIT(REGEXREPLACE(INDEX(REGEXEXTRACT(A1,"(\[(\n|\s|""|\w|,)+\])"),1),"(\[|\]|\n|\s|"")",""),",")
You can adapt this formula to suit whatever ...
1
I tried this and its actually worked .
function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var r = sheet.getRange("L2").getValue();
if (r == 'ADD'){
// Add your code here
}}
0
Since no other answer was found anywhere I pursued the problem till I found something that works for me.
function SendMail() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('SheetName'), true);
var sheet = spreadsheet.getActiveSheet();
var subject = spreadsheet.getRange("B2")....
0
The code you describe gets the Sheet name, but it is entirely out-of-context in your scenario - you have no linkage between the name of the employee and the relevant Sheet. Instead, I suggest that you create a Helper Sheet containing a two column table:
Column A Column B
Emp Name QA Name
Jame Doe Employee#1
John Doe Employee#2
Then ...
0
Google Apps Script hasn't a postEdit trigger but you can achieve the result you are looking for by using JavaScript conditions.
First, some basic tips,
always use var to declare your variables otherwise they will be declared on the global scope and this could lead to some problems.
make use of the event properties, in this case, instead of getActiveCell() ...
0
This is commonly known as "mail merge".
If you have to do this just very few times a day and have very few placeholders, using the built-in find & replace could be good enough but if you have several placeholders and/or have to do this tens or hundreds of times a day it worthy to create an script by using Google Apps Script or use one already published ...
0
Wouldn't a simple find and replace do the same thing?
Find: {name}
Replace with: Fred
2
Use batch operations instead of unit operations.
In other words, make an effort to avoid or reduce reading / writing of single cells specially in loops. When it is possible read all the values that you will need on a single operation, i.e. instead of using a for loop and getValue() to read a single value on each iteration use getValues() to read all the ...
0
This answer addresses the OP's goal of selecting the "next sheet" option from a drop down list in a dialogue. It should be considered as an example of one method, and possibly requires further fine-tuning, error checking and/or validation.
The OP has an onOpen menu. Currently this lists several function, each of which is a permutation of the source and ...
-2
If using QUERY is applicable you can do it.
Example:
QUERY({'your sheet name'!A1:W293},"select Col3,SUM(Col5) where Col1>=datetime '2019-08-01 00:00:00' and Col1<=datetime '2019-08-31 23:59:59' group by Col3 order by SUM(Col5) desc limit 10 label Col3 'Track', SUM(Col5) 'Sum'")
or
QUERY('your sheet name'!B:E, "select E where (D='" & $B9 & "'...
1
Google Sheets user interface doesn't include a way to locate a sheet by name.
At the bottom there is a tab for each sheet, but they aren't sorted alphabetically and some sheets could be hidden; also there is a button that shows the list of sheets shown and hidden but this list isn't sorted either.
Google Apps Script could be used to find the sheet. I.E. ...
Top 50 recent answers are included
