Questions tagged [google-sheets]
For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.
1,821
questions with no upvoted or accepted answers
2
votes
0
answers
66
views
Using INDIRECT with sheet names taken from a range
This formula I created worked in Excel:
=ROUNDUP(SUMPRODUCT(SUMIF(INDIRECT("'"&$N$5:$N$41&"'!A6:A100"),A5,INDIRECT("'"&$N$5:$N$41&"'!C6:C1000"))),0)
This formula is in my main sheet, ...
- 29
2
votes
0
answers
3k
views
How to copy from Google Sheets into a Drawing in another Sheet on macOS Chrome
I'd like to copy an object from one Google Sheet to a Drawing embedded in another Google Sheet. From what I've read, this must be done via the Copy / Paste commands within the Google Sheets web app. ...
- 121
2
votes
1
answer
1k
views
Email notification if cell value goes below set amount
I'm having trouble finding a Google sheet script that does what I need so I figured I would ask if someone could help me. Basically I have a simple sheet setup to track inventory of one item. There is ...
- 21
2
votes
0
answers
692
views
Random #REF! errors
I am a new Google Sheets user, but have been using Excel or equivalent for over 25 years. I have an issue with #REF! errors associated with Google Finance lookup in a sheet I wrote to pull stock ...
- 21
2
votes
0
answers
625
views
Chart in Google Sheets is duplicating data labels
I'm using Google Sheets and a triggered Apps Script function to create a waterfall chart. Basically this involves breaking apart each value into a base (either visible or invisible), a falling value (...
- 141
2
votes
1
answer
999
views
Querying multiple ranges from form responses to new sheet
Recently, someone on this forum put together a formula for me. It works on a sheet pulling responses from a google form. I'm trying to model a new one after that but I'm having trouble. The working ...
- 21
2
votes
0
answers
371
views
Filtering/consolidating data from multiple areas in Google Sheets
I am collecting data on a Google Form where users select a name then answer specific questions depending on what name they select. This leaves the resulting response spreadsheet with many blank ...
- 317
2
votes
1
answer
1k
views
How to use Google Scripts w/ spreadsheet and jotform to auto sort so the newest entry is always listed on the top row?
I'm trying to setup a form and spreadsheet database to track inventory on a daily basis at a remote location. The idea is that the person at the remote location can update our stock via a simple ...
- 29
2
votes
1
answer
2k
views
How do I get GOOGLEFINANCE to return only the date and not the date and time of stock price history?
=GOOGLEFINANCE("NASDAQ:GOOGL","close",date(2021,1,1),today(),1)
returns closing history in two columns "Date" and "Close" since Jan. 1, 2021. The date column ...
- 121
2
votes
1
answer
552
views
Drag to fill/autocomplete dates only increases year, not day
Please see screenshot below. When I dragged on the blue square in highlighted cell, I only get the result below.
I have set correct region in sheet settings. I have set the correct date format in the ...
- 21
2
votes
2
answers
666
views
Adding 1 to a Cell with VLookup
So this is my first time trying to write a script, and the sheet I am working on is here.
The script I'm trying to run would allow me to push a button and have it use Vlookup to add 1 to a cell in ...
2
votes
1
answer
2k
views
How to sum amount with a criteria separately by day in Google Sheets?
I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month.
The file for monthly account contains a couple of sheets - each ...
- 21
2
votes
2
answers
3k
views
How can I quickly change the color of text in a cell in Google Sheets?
How can I quickly, with either a single or double click, assign a color to the text in a cell in Google Sheets?
In order to quickly determine the status of certain pieces of data in a spreadsheet I ...
- 129
2
votes
1
answer
260
views
How to insert cell text from one cell into an ImportRange Function
Hello! so I have an issue where I am trying to do is have multiple functions in C, D, and E where a link is required. Instead of copying and pasting the link inside all the functions in specific ...
- 21
2
votes
1
answer
460
views
Google Sheets row minimized to almost nothing when no data is present but needs to auto expand to be fully seen ONLY when data is present
I've got a Google Sheet I'm creating for settlement purposes and I have 8 variables that only get used sometimes. Each variable has its own line devoted to it. For example, this load has a scale ...
2
votes
2
answers
5k
views
Duplicate sheet but keep protected range with exact same user as only editor?
The following code duplicates a sheet based on a template, and copies over the protected range permissions from the template sheet to the new sheet.
However, the permission range is now there in the ...
- 41
2
votes
2
answers
3k
views
In Google Sheets Query Max Date from Dates in Column A for a particular Project ID stored in Col B in Tab 'Data' from Tab 'Main'
Tab Data is as under
Date_A Project_ID
01-May-18 EL-PR-18-08709-1
02-May-18 EL-PR-18-08709-1
01-May-18 EL-PR-18-08709-1
03-May-18 EL-PR-18-08709-1
02-May-18 EL-PR-18-08709-1
01-May-18 ...
- 19
2
votes
1
answer
296
views
Moving linear regression on google spreadsheet
I have a table with four columns:
A: date
B: values
C: projection
D: short-term projection
The first two are hardcoded values. We admit that dates go up, while values mostly go down. I used a ...
- 21
2
votes
1
answer
2k
views
Insert Text from Spreadsheets to Data Studio?
Is there a way/workaround to display a text value (not numeric) from Google Spreadsheets in Data Studio Report?
I found a way to display numeric values in Data Studio, but I couldn't replicate the ...
- 133
2
votes
1
answer
1k
views
Copy one Google Spreadsheet to another
I am developing a Google spreadsheet that I want to make publicly accessible to a community. I've released the first version of it, and now everybody in the community has that first version's link ...
- 73
2
votes
2
answers
57
views
In Google Sheets, how to achieve the “unwinding” of rows in a table that contains columns with multi-valued data
In Google Sheets, how can I achieve the following “unwinding” of rows in a table that contains columns with multi-valued data:
Column A
Column B, multivalued
First
A,B
Second
C,D
It transforms the ...
- 150
2
votes
1
answer
3k
views
Reference a Google Spreadsheet Row containing specific Text within Google App Script for spreadsheets
I am having trouble finding a function that can reference a row (the number of that row) containing specific text as criteria.
I thought that VLookUp would work, but it's not really. Ideally it's ...
- 31
1
vote
1
answer
10
views
Google sheets slider for a multi-valued cell
I have cells in Google sheets that have multiple values, such as:
Continent
Law
North America
Sarbanes Oxley, Freedom of Information Act
Europe
Digital Markets Act, Digital Services Act
By default,...
- 11
1
vote
1
answer
18
views
Adding a total in a cell when a checkbox is ticked in an adjacent cell
On my budget spreadsheet, in the 'amount' column is the different direct debits going out each month, then I tick the checkbox in the 'gone out?' column when it goes from my account. I'm wanting to ...
- 11
1
vote
1
answer
8
views
im trying to get a group of cells and there values repeat along a row. i have values entered from b5:bl5 and i need it to just keep repeating
Google sheets- entered values in B5:BL5 and i need it to repeat across row 5 all the way to og5. I need it to do it automatically. I have tried a scrip that doesn't work cause i don't know what I'm ...
- 11
1
vote
1
answer
30
views
Subtracting lunch break from hours worked
I'm trying to calculate the total hours worked, not including lunch breaks. I'm using standard numbers for hours worked such as 8, 10, 12 etc. However, it needs to reflect a 30 minute break for any ...
- 13
1
vote
1
answer
61
views
How to combine ARRAYFORMULA + QUERY?
"Please advise on how to correctly input the formulas to get the desired result as shown in the picture.
Task:
https://docs.google.com/spreadsheets/d/14tpPliM_3b7RIikSimvdzLjkFq3CexPKbtOEuD4aIKU/...
1
vote
1
answer
42
views
Format a numbered list in google docs into a table template?
I have a list with a url, an emoji (of a set of 3), and a name in a numbered list.
Taking a list like this:
https://news.ycombinator.com/item?id=38122061 🥾 D. onohoe
https://news.ycombinator.com/...
- 11
1
vote
3
answers
32
views
How would I SUM column values while excluding any that precede the text string "reset"?
I have a spreadsheet where the hours of wear for pieces of equipment are logged:
Data is logged in new rows as additional wear-hours are put on the equipment.
From time to time, equipment is replaced,...
- 11
1
vote
1
answer
89
views
SpreadsheetApp.openById gives unauthorised error
I am trying to trigger a function in Google Apps Scripts, and am using openById("Sheet_Id);
But on using it, I am getting this error:
Exception: You do not have permission to call SpreadsheetApp....
- 11
1
vote
1
answer
130
views
Multiple large GOOGLEFINANCE formulas make spreadsheet unresponsive
Problem
In Google Sheets I have huge GOOGLEFINANCE formulas that return accurate results however the sheet is often unresponsive, and can take a few hours to recalculate when reloaded.
Is there a way ...
- 11
1
vote
1
answer
13
views
Google Sheets keep data together when a referenced sheet is sorted
I have 2 sheets on my google sheet. Sheet 2 references Sheet 1 for it's first 3 columns. I did this with an arrayformula(theSheetName_Columns).
The issue is when I add data to the other columns in ...
- 11
1
vote
1
answer
30
views
How to sum data on one row from multiple selected Google Sheets
I am tracking paid advertising key performing metrics on both Google & Facebook ads platforms on two separate Google Sheets, however, I'd like to combine both datas collected into one master sheet....
- 11
1
vote
1
answer
78
views
Clear cell value if cell in adjacent column is blank
In Google Sheets I have two data columns, S and T.
What formula would clear the value in T if the value in S is not RUN?
Sample Data
Before
S
T
1
...
1
vote
1
answer
47
views
How can I retrieve a 2D array by its header?
What I am trying to accomplish here is to build a spreadsheet for meal planning.
On the left side, with the column header called dish ideas, I am logging all the individual ingredients of a recipe.
On ...
- 11
1
vote
1
answer
54
views
Sum number values associated with letter values
In Google Sheets, I have a system of scoring that uses letters paired with numerical values.
letters
values
A
26
B
25
C
24
D
23
E
22
F
21
In my data, I have columns that are populated with ...
- 11
1
vote
0
answers
26
views
How to convert a Splitting formula from Excel to Sheets (given that Textsplit is missing)
I'm attempting to copy the function of the following Excel sheet into Google Sheets and running into a snag.
https://1drv.ms/x/s!ApTGA-odt1HWhzGjuVHPn9bJyr5G?e=ZxtUCi
It takes the list from A2 chops ...
- 13
1
vote
2
answers
65
views
different results for COUNTIFS on date range when using > vs. >=
I haven't quite been able to wrap my head around this one, but I've got a spreadsheet that is a simple list of dates, formatted thusly:
11/1/2022 0:12:54
11/1/2022 6:45:43
11/1/2022 7:13:15
... and ...
- 11
1
vote
1
answer
65
views
Locate a text string and return the width of the range containing it based on whether it's in a single or merged cell
I have calendar data in my spreadsheet. Each row is a day, and each column is a half-hour increment of time from that day.
When I add events to the calendar I block off the time by merging the ...
- 11
1
vote
2
answers
27
views
How to specify formula for the entire column in google sheet?
Is it possible to specify formula for the entire column?
For example:
A
B
1
=A1 * 2
2
=A2 * 2
After I insert a new line between line 1 and line 2, the B2 cell will be empty.
A
B
1
=A1 * 2
3
2
=...
- 111
1
vote
0
answers
41
views
Return value based off multiple conditions
I have this formula I am trying to get working but I am struggling to do so for an unknown reason. I wondered if anybody on here might be able to help. I am starting to think this may not be totally ...
- 11
1
vote
1
answer
17
views
Google Sheets formula to round up columns to nearest thousand and sum
So i have a column with numbers and want to sum the column, but i want them to be rounded up to the nearest thousand.
=SUM(ARRAYFORMULA(ROUNDUP(J4:J ,-3)))
I tried this however I am just getting a ...
- 11
1
vote
0
answers
17
views
Google Sheets ladder linked with Google Drive folder
For a project at school, I have around 340 pages (170 spreads) labeled with the contents of the page/spread in the cell in a Google Sheet. I also have a Drive where every spread has a folder (ie 002-...
- 11
1
vote
0
answers
16
views
Publishing one sheet from a Google spreadsheet
I have a Google spreadsheet which contains multiple individual sheets. In the upper right corner, the blue "Share" button shows "Anyone on the internet with the link can open it". ...
- 11
1
vote
1
answer
75
views
Google sheet: find a Word from a range, in a string and return corresponding Word from the range
Column A contains text strings
Range aa:bb (in another sheet):
aa contains keywords
bb contains result-Words
I would like to put a function in for example cell B2 which:
Checks If any of the Words in ...
- 11
1
vote
1
answer
27
views
Conditional Formatting to Highlight Cells in Row if any number is present, but NOT if a formula or blank cell is present
I am looking for a conditional formatting rule that will highlight cells in a given row that contain a number only, but NOT a formula and NOT blank cells.
Example of the sheet:
Number of cases ...
- 11
1
vote
1
answer
69
views
Unable to create an ARRAYFORMULA that will AVERAGE the results of a QUERY
I have a sheet data that tracks client transactions.
Each row is a transaction
The columns are:
LAST NAME
PHONE NUMBER
SUB
TOTAL
TIP
PERCENTAGE
The TIP and PERCENTAGE columns are calculated based ...
- 11
1
vote
2
answers
43
views
Add cell value in query
When doing a multi-sheet query, I would like to bring in the value of a cell (A2) from each source sheet to a new column in the results.
The query:
=QUERY({CCL!A4:M; HOOD!A4:M; AAL!A4:M; BAC!A4:M;
...
1
vote
1
answer
282
views
Google Sheets Query, filtering with a range of values
I want to query data by checking if the data_range's column C (invoice#) matches this tab any value in column B (invoice#), while this tab column B will have variable data size ( copy from another ...
- 11
1
vote
1
answer
536
views
GoogleSheets - How To CountIF within Date Range
Good evening,
Thank you in advance for any assistance you can provide.
I am trying to sum (countif) the total amount of matching items from one sheet, if they match a certain timeframe range (e.g. one ...
- 11