56,898 questions
0
votes
1
answer
49
views
Prevent a new tab from opening when opening WhatsApp from Googlesheet OnEdit
as per below code, from GoogleSheet, I wanted WhatsApp to open with some specific text ready to be sent when user sets a cell to "Ready to Print". The problem is every time a column in ...
- 11
0
votes
0
answers
62
views
Conditional Formatting an Expanding Table [closed]
I have a table within a sheet for inventory. I have conditional formatting that will check the stock levels and highlight the row red when it's 0. This works perfectly.
However, when the table expands ...
0
votes
1
answer
98
views
Why is my exported Google sheets adding trailing zero to character when read by read_excel in R?
I have a data sheet that I am entering in Google Sheets and importing into R as an .xslx file. One of the columns in the datasheet is a character variable representing the specimen numbers of each ...
- 424
Advice
0
votes
3
replies
46
views
Counting number of occurrences of names in a single cell
Is there a way to count the number of times a name appears in a single cell? These are goal scorers in a match.
Eg - In Cell A1 (the team won 6-0) - James Smith, Bob Jones, James Smith, Bob Jones, ...
2
votes
1
answer
89
views
GmailApp.sendEmail not working when rows arre added to Google sheet
I have an application (running on an IoT Boron device from Particle) that posts error messages to a Google sheet via a Webhook Web Form. The sheet serves as a log of application faults and this has ...
- 1
-1
votes
0
answers
67
views
Is Looping possible in Google Workspace Flows [closed]
So, while building automation agents with google workspace flows, I'm documenting a number of files, and extracting data from the files, and inserting them in google sheets, using the flows ...
- 1
1
vote
2
answers
77
views
How to display array results in a grid instead of a single row or column?
I have a single cell with data split with comma + space, like so:
ENG, DE, FRE, ESP, CHN, KO, PL, CZ, PRT, JA, RU
I can easily create an array with 1 string per cell, with SUBSTITUTE() and SPLIT() ...
- 143
1
vote
1
answer
53
views
Copy and paste with .getValues / .setValues, but clearing the array inbetween
I have a table which looks like this:
I want to copy the first two columns of data and insert them before the EOF, but empty them before the paste. The end result should look like this:
So far, my ...
- 181
3
votes
1
answer
66
views
How can I create a table using ARRAYFORMULA that concatenates all items meeting the same criteria in one cell?
Sample spreadsheet
I've been trying for the life of me to figure out how to do this and cannot.
I have a table: ColA is items, ColB is their category.
Item
Category
Apple
Fruit
Orange
Fruit
...
- 33
0
votes
2
answers
103
views
LAMBDA( value; value) isn't a identity function [closed]
The formula
=SPLIT("1\10/10\00002.2";"\")
doesn't result equal to
=MAP(SPLIT("1\10/10\00002.2";"\"); LAMBDA(v; v))
At the former, implicity conversion is ...
- 384
Best practices
0
votes
7
replies
103
views
SPLIT without implicit conversion
I am experiencing problems with this section of my formula
=LET(
args; MAP(SPLIT("11/11"&CHAR(10)&CHAR(10)&"1"
; CHAR(10)&CHAR(10)); LAMBDA(v; TO_TEXT(v)))...
- 384
0
votes
1
answer
38
views
How do you fetch all the needed data in ALL the links in a column?
I can't really figure out how fetch and combine all the data in ALL THE LINKS in one Google Sheet.
This is the location of the the links:
SECTION LINKS'!B2:B
And this is what I want to get in every ...
0
votes
1
answer
58
views
Function Filter - How to not apply filter on a specific cell value?
In Google Sheets, I have a database on which I want to apply a filter depending on 10 criteria from 10 columns, using values from 10 specific cells. I can do this, but I would like that the filter ...
0
votes
1
answer
67
views
How to apply cell background color as Theme color in google sheets via apps script?
Hopefully, the title is clear.
What I am NOT asking.
How to update the cells background color to match the Theme via hex color code.
How to update the spreadsheet theme colors themselves via appscript....
- 43
-4
votes
2
answers
174
views
Move Labor Cost Formula from Sheets to Excel
Problem
I have created quite a convoluted formula in Google Sheets that Sheets doesn't seem powerful enough to handle for more than ~80 rows of data, so I wanted to try moving it over to Excel. Seems ...
- 27
-2
votes
2
answers
76
views
how do I tell which external Google Sheets a cell is pulling data from?
In a google sheet cell, I have the following formula:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1xMGMl4GFs_2ZDxCrtfRMjG6CPMCdosiaVeCr47YYfb4","Monthly payments!B3")
it's ...
- 97
Advice
0
votes
0
replies
34
views
Creating PWA for individual docs, sheets etc
I would like to create PWA that acts as a standalone app in osx for individual docs (like notes, financial statements etc). But Google PWA redirects the PWAs to the landing page for google docs or ...
- 85
-1
votes
0
answers
65
views
Connected Sheets with BigQuery
I'm trying to set up connected sheets in Google Sheets, which according to the link below, requires Delegated Access and the access set up in Organization Settings. Is it possible to set these up if I ...
- 9,562
0
votes
1
answer
73
views
Filter in Filter or Query/array for multiple sheets
I'm a long time reader, first time poster.
I'm having a problem showing invoice details for each company id for this sheets.
https://docs.google.com/spreadsheets/d/1iTJ8PR3snsbZKh01-...
Advice
0
votes
3
replies
60
views
How to convert a column of duration text strings to numbers in minutes
I have a column (of known length) of duration text strings that contains potentially strings that are not duration. And I want to convert just the duration text strings to numbers in minutes.
For ...
- 10.1k
Best practices
0
votes
4
replies
69
views
Handling Multiple Sheet Inputs
I have a spreadsheet that receives data inputs externally. That data is used by sheet formulas to calculate an output which is then collected via an HTTP request.
What is the best way to allow google ...
0
votes
1
answer
86
views
Checkbox behavior
So as per previous suggestions, I'm using checkboxes to replace the .getUi method so my project will work properly on mobile devices. I've come across a strange scenario and I wanted to confirm this ...
- 181
0
votes
0
answers
79
views
Shift rows up/down in line with a dynamic list
I'm trying to get the data in rows to move up or down based on if col A grows or shrinks. However col A's data is defined by the range I want to shift. I haven't been able to find something that looks ...
- 9
Best practices
0
votes
4
replies
53
views
Google sheet IMPORTRANGE
I would like to receive your help.
I have 2 google sheets.
Sheet 1
Column A: all the rows contains codes
Column C: somethings in first 2 rows, but this is variable
Sheet 2
Column E: I would like to ...
2
votes
1
answer
74
views
Stacked column chart to use column A as header in App Script
I have this table and the chart I want as below:
To acheive this I have to switch rows/columns, and use column A as headers.
However I cannot find the corresponding command for the checkbox "Use ...
- 305
Advice
0
votes
3
replies
139
views
Calculate Caseload to Compute Labor Cost
I am trying to calculate the employee labor cost per case at my company in Google Sheets. At my company, a client calls in saying they want to buy flowers from us. At this point, we open a case for ...
- 27
1
vote
2
answers
105
views
Checking for changed checkbox value
To get around the fact you can't pop user dialogue windows on mobile devices, I have created a simple function which posts any error messages to a cell and waits for the user to check a checkbox to ...
- 181
0
votes
2
answers
81
views
How to reference the row number of a cell which is referred to in another cell?
In column D I have a simple reference to a cell in column R (e.g. cell D6 =R32)
In other columns I have formulas which need to include a reference to cells with the row number refered to in column D (...
-3
votes
3
answers
122
views
How to have an inline array of a specific number of blank cells?
I want to do, say in cell A1
=let(n,5
,vstack(CREATE_BLANK_ARR(n)
,"meaningful contents to follow"))
where later arrayformula(isblank(A1:A5)) output TRUEs
How do I implement ...
- 10.1k
0
votes
1
answer
69
views
How to filter data into another spreadsheet based on two values in the same column in google sheets
I'm making a book spreadsheet where I'd like formats to filter into a spreadsheet for physical books (Hardcover and paperback) and another for Digital books (ebooks and audiobooks). I can only seem ...
1
vote
1
answer
82
views
Filtering a 2D Array
I have a column of checkboxes, which drives some downstream processing if they are checked. At the moment, my code creates the array of checkbox values but only uses it to check if any checkboxes have ...
- 181
0
votes
1
answer
122
views
Looking for a specific word in a Title in a cell then pushing a numerical code to a different cell in Google Sheets
My situation in Google Sheets: I have a list of target words and phrases in column A (as few as 20 to as many as 400), in column B I have 3 and 4 digit number codes assigned to each target word and a ...
- 1
-3
votes
2
answers
70
views
Split randomly names into 6 different groups [closed]
is there any formula which will help me split randomly a dynamic list of names into 6 different groups?.
I would like each group to have as equal as possible amount of names.
Here is a google sheet ...
- 9
2
votes
2
answers
145
views
How do you use an array formula to count the number of times SO FAR a value appears in the array up to where the value is in the array?
I'm trying to use an array formula in Google Sheets in order to get how many times so far in an array a value has appeared. Specifically, I'm using an "array" of single upper case letters (...
- 25
1
vote
1
answer
79
views
How to extract multiple matched columns
I can extract matched columns using the FILTER function in Google Sheets, but I need help making it work when there are multiple matches. For example, if the header row contains the same value in ...
- 611
0
votes
1
answer
61
views
breakglass/elevated permissions? [closed]
I have a script which applies protection (ie, performs a .removeEditors) via an installed trigger to a sheet row depending on some user input. There is a possible use case whereby the user (not the ...
- 181
1
vote
1
answer
61
views
copyTo Error: Exception: The parameters (SpreadsheetApp.Range,number,(class)) don't match the method signature for SpreadsheetApp.Range.copyTo
I have a spreadsheet from last year and it worked OK then. Without edits, I opened it and now it throws this execution error:
Exception: The parameters (SpreadsheetApp.Range,number,(class)) don't ...
- 1,771
0
votes
1
answer
90
views
How to repeat a series of rows, changing their shared date?
I'm using an app called OneUp to schedule social media posts, and trying to set up a google sheet (here's their page on that) to bulk schedule.
Right now what I have (simplified for clarity) is ...
0
votes
0
answers
55
views
google sheets API only returning single chart
I have a little program that is supposed to return all of the charts in a Google Sheets document. The program is as follows:
from google.oauth2.credentials import Credentials
from ...
- 150
1
vote
1
answer
102
views
Google Sheets script - slow running
I have the following code to copy the last row (effectively used as a template) and paste it a user-defined number of times into the sheet, effectively inserting new rows:
function InsertNewRows() {
...
- 181
1
vote
1
answer
70
views
Google Sheets script .protect() working intermittently
Depending upon a specific input in a set of cells on my Google Sheet, I want to be able to freeze a particular row and stop it being updated.
I have the following script:
function testFreeze() {
...
- 181
-3
votes
2
answers
188
views
How can I stack each 3 sets of 4 rows? [closed]
I have a report that vertically stacks yearly data made of two columns:
The first column shows the year and each of its 4 quarters (format: "YYYY Qx").
The second column shows a $ amount.
...
- 47
0
votes
0
answers
51
views
Gmail API 403 "Delegation denied" error using Service Account DWD (Apps Script) despite Super Admin role
I am running a Google Apps Script to programmatically update Gmail signatures for users across my Google Workspace domain using a Service Account with Domain-Wide Delegation (DWD).
Despite confirming ...
- 53
-2
votes
1
answer
77
views
How to count NULL values in Looker Studio when using a date range filter (using DS_START_DATE / DS_END_DATE parameters) [closed]
I’m working in Looker Studio (Google Data Studio) using a Google Sheet as my data source.
My dataset looks like this:
Each merchant can have multiple shipments (or none).
I have a date range control ...
- 27
0
votes
1
answer
70
views
How to use ARRAYFORMULA with multiple criteria XLOOKUP or INDEX MATCH? (SHEETS)
I want to add in some additional information to a sheet that is populated by Google Forms from another tab in the same worksheet.
I want to have a formula which looks up a value based on multiple ...
- 1
-3
votes
2
answers
103
views
Get Previous Month Date - Google Sheets
Using the formula =EDATE(A2, -1) to subtract one month from a date that falls on the 30th, when it reaches February, which has only 28 days, the formula returns the 28th. For the following months, it ...
1
vote
1
answer
74
views
Google Sheets, Trim function not removing leading space
I've managed to split the values in a multiple-choice data validation cell into a single column using this formula in Google sheets:
=ARRAYFORMULA(
QUERY(
SPLIT(
FLATTEN(
...
0
votes
1
answer
101
views
Google Sheets Formulas
I was wondering if there is any way (formulas) that can change a cell's color when we are approaching the date previously entered in a cell. What I am looking for is a way to notify myself of upcoming ...
- 1
0
votes
2
answers
137
views
How can I count the occurrence of a text in other dataset?
I have 2 tables. This is the table that will be shown as a report:
File
Delay
1076759
1066123
1081690
1038312
This second table is the data source (different spreadsheet):
Entry Date
File
Status
09/28/...
0
votes
1
answer
70
views
Sort Tabs Alphabetically keeping Certain Tabs first and ignoring Hidden Tabs
I have a file that includes a variety of reference sheets and sheets that are only used at the end of the academic year plus a tab/sheet for individual students. Students come and go so I need the ...
- 17