New answers tagged google-apps-script
0
Please read https://developers.google.com/apps-script/guides/support/best-practices. In summary, whenever is possible you should avoid to use Google Apps Script methods in loops as they are slow. In example, instead of making the changes one cell at a time build an Array of Arrays of the background colors, then apply all the changes at once (instead of ...
0
Those Range.getValue() calls are missing their parentheses ().
0
The script has several problems.
Start by replacing
var ss = SpreadsheetApp.getActiveSheet();
by
var ss = SpreadsheetApp.getActiveSpreadsheet();
also replace getvalue by getValue() and setvalue(a-b) by setValue(a-b)
Resources
https://developers.google.com/apps-script/guides/sheets.
0
Google Apps Script doesn't support the current version of Google Sites. You could embed your Google Drive "site" by using the built-in Google Sites functions to embed content i.e. you could embed the a folder, for details see the official help article Add Google files, video & more
The only way to automate embedding content and adding links ...
0
SpreadsheetApp.getActiveSpreadsheet() is returning null. This happens when the script project is not bounded to a spreadsheet and when the bounded project is opened by using it's URL or from the Google Apps Script hub (https://script.google.com) instead of opening from the container.
There are two possible solutions,
use your script in bounded project
...
1
The regex /$[0-9\s]+/ attempts to match numbers and whitespace immediately after the end of line. That will never match anything. Use /[\s\d]+$/ to match numbers and whitespace immediately before the end of line.
You apparently to want to add numbering to the end of headings. The split() and if seem unnecessary and can probably be removed. Simply use this:
...
0
Try something like this:
function clearoddrows() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheetname = "Sheet1"
var sheet = ss.getSheetByName(sheetname)
// get the range B3 to B101
var startrow = 3
var endrow = 101
var column = 2
// get the range
var range = sheet.getRange(startrow, 2, (endrow-startrow+1))
// ...
1
A workaround is pressing Esc right after the mouse click on the button.
It's slightly more comfortable than using mouse, but it's still an intermediate step.
0
There are a few ways of achieving this:
1. The LAMBDA function
Excel has the LAMBDA() function which can be used for this purpose, but unfortunately it has not been implemented into GSheets as of Jan 2022. Hopefully it will become available at some point.
It would look like this:
=LAMBDA(x, IF(x < 0, x + 1, x))(A1+B1)
Basically it creates a function on ...
0
Though you are a new user, you will have to to learn scripting. This is somewhere to start:
Overview, Guide to Automating Tasks with Apps Script, the Extending Google Sheets page, Triggers and Event Objects and the Mozilla Javascript Tools
This script should meet your needs
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet()
// ...
0
For anyone who is looking for a way to move a sheet to the far left or far right, or anywhere without adding a script to your sheet. I built an add-on to do so: https://workspace.google.com/marketplace/app/sheet_mover/797282136202
0
Remove && ss.getActiveSheet().getName()=='Sheet1' in the if statement.
This line is limiting the script to recognise chnages to just "Sheet1". If you remove this part of the condition, then it will work on ANY sheet
0
Try this (untested)
// uncheck every checkbox in Column A of every sheet except for the last sheet
function ClearAll() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets()
var sheetscount = sheets.length
for (s=0;s<sheetscount-1;s++){
var LR = sheets[s].getLastRow();
var range = sheets[s].getRange(...
3
Part of your problem is that SPLIT, when used as an argument to a function, returns a string. Say your C1 cell contains the text 1,C. Your formula
=GetNumber(SPLIT(C1, ","))
is actually equivalent to
=GetNumber('1C');
If you are somewhat familiar with JavaScript, you could instead do the split operation within your function code:
function ...
0
Try this:
const mainSheet = ss.getActiveSheet();
...and this:
const pdfFile = DriveApp.createFile(ss.getBlob().getAs('application/pdf').setName(name));
const folder = DriveApp.getFileById(ss.getId()).getParents().next();
pdfFile.moveTo(folder);
0
A user might "inherit" a Slides document and be unable to establish whether/how document defaults have been modified. In such a case, an Apps script may enable the user to obtain more specific information. The following script demonstrates some minimum steps...
The following script lists:
Layouts: The available layouts (by name) in the ...
0
You will find worthy to spend some time improving your JavaScript and programming knowledge and skills. In this specific case you could create a function to be called by another function (some people call them "helper function") by passing the URL as parameter or make use of Array methods like Array.prototype.forEach() or Array.prototype.reduce(), ...
1
Sumif
=INDEX(
IF(
A3:A = "", "" ,SUMIF(
ROW(
A3:A
), "<=" &
ROW(
A3:A
) ,A3:A
)
)
)
Option suggested by Adam.
Drawbacks:
the last parameter of SUMIF needs to be always a "valid range"
not easy to add ...
0
To make this script working you need to change comma "," to semicolon ";" inside formula.match(...) section
0
I was interested to know whether your outcome could be achieved with a formula (yes, I do understand that the result would be dynamic not static, but it was an interesting problem.
Try this formula:
=query({ARRAYFORMULA(query(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE( IF(IFERROR(SPLIT(B2:B, ","))<>"", "♦"&...
1
It is unclear why you would need to use a script, because this can be done with a plain vanilla hlookup() formula, like this:
=arrayformula( iferror( hlookup(A6:A15, D6:F7, rows(D6:F7), false) ) )
See the new Solution sheet in your sample spreadsheet. To read the data from another spreadsheet file, use an importrange() in place of D6:F7 and 2 in place of ...
1
Use getNextDataCell(), like this:
const spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getCurrentCell()
.getNextDataCell(SpreadsheetApp.Direction.DOWN)
.offset(1, 0)
.activate();
0
Here is an updated version that does not use regex. The regex started breaking for no good reason.
function has_strikethrough(reference) {
var sheet = SpreadsheetApp.getActiveSheet();
var formula = SpreadsheetApp.getActiveRange().getFormula();
var fParts=formula.split(')').shift()
var rang=fParts.split('(').pop()
try {
var range = sheet....
Top 50 recent answers are included