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.
8,681
questions
0
votes
1
answer
57
views
App Script for getting data from one cell
I have a large database with unstructured data. In the below pic column A contains the 14 districts of Kerala. Column C:G represents the customer details. If any of the cells from C2 to G2 contain a ...
- 21
0
votes
1
answer
209
views
Merging two timeline charts with different date-sets in Google Sheets
In Google Sheets I have two timeline charts with source data Sheet1!A1:B100 and Sheet2!A1:B100. Dates in Sheet1!A and Sheet2!A mostly do not overlap - Sheet1!A are more or less random (datapoints are ...
- 103
0
votes
1
answer
40
views
Query ignoring text
When I share data with sub-subcontractors, I use the QUERY function to return a subset based on a company name match. I never had any issues in the past, but lately QUERY has not been returning values ...
- 1
0
votes
0
answers
47
views
Creating Multiple PDF's from Google Sheets from a client list
I have a Google Sheets spreadsheet with logged times for projects. I need the data from each client to create a PDF with their times displayed. See attached image. This example has four clients, so I ...
- 9
-1
votes
1
answer
30
views
How can I used the same dropdown list across multiple tabs in a sheet?
I have a Google Sheet with multiple tabs in it. One for every week of the year. On one of those tabs, called MasterList, I also have a list of items from A1 to A50. I've created a dropdown of those ...
2
votes
1
answer
149
views
Does importrange not work on Google Sheets embedded XLSX files?
The ImportRange documentation mentions nothing about not supporting XLSX embedded files.
But when I try it, it claims it has no permission (whereas I'm an editor in the file I try to import).
Does it ...
- 1,204
0
votes
1
answer
29
views
Return ' MATCH ' if cells contain equal value
I have a database with different names for the same phone number. But some of the mobile numbers contains same names as listed below. So I have to Match mobile numbers having same names as shown in ...
- 21
2
votes
1
answer
44
views
Sum of Cells (supplementary question)
This question which specifically referred to summing a column of numbers until a break and then subtotalling on the next following line was answered very helpfully by this answer.
Supplementary to ...
- 541
2
votes
0
answers
133
views
Moving data generated by a Google form from the response tab to another tab
I'm trying to copy data from a Google Sheets tab linked to a Google form into a new tab. I used the function ='Form Responses 1'!B2, then copied the formula in the other cells of the column. But when ...
- 21
1
vote
1
answer
46
views
Sum of Cells in the blank cells in the next column. ( Formula or Appscript)
I want to sum the cells until I get a blank cell in the column B then add that sum in next column. In the below pic sum of C2:C4 should come in D5,sum of C6:C10 in D11 and so on. Database is so large. ...
- 21
0
votes
2
answers
35
views
Unique across columns
I have a spreadsheet that looks like this:
Company
Fund
Date
Value
A
X
30/09/2022
12
B
X
30/09/2022
15
E
X
30/09/2022
31
A
X
31/12/2022
10
B
X
31/12/2022
20
C
X
31/12/2022
15
D
Y
31/12/2022
22
...
- 629
3
votes
1
answer
48
views
Filter a list of lottery results for those matching at least 4 numbers
I have a list of 3,000 winning lottery numbers. Each winning lottery number is on its own row and is comprised of 6 individual numbers, each in their own column. The winning number's draw date is also ...
- 33
3
votes
1
answer
54
views
Formula error - Column result based on multiple rows values
Known values:
A
B
C
D
Tenant
Flat no.
Duration stayed
Energy consumed in flat
To find out:
E
Energy consumed per tenant
Condition:
I have to consider the unique value of column A as an ...
- 33
0
votes
0
answers
11
views
Gather financial data from yahoo with command IMPORTHTML() [duplicate]
already at the beginning of the year I created a table (google sheet) that is intended for collecting financional data and creating reports from the yahoo server (Specifically, about 15 pages). I ...
- 1
2
votes
1
answer
74
views
Google Sheets stacked bar chart showing 1 extra value and legend
Consider this Google Sheet:
6/30/2020 1/1/2021 1/1/2022
Consultant 3 4 6
Manager 0 1 1
Administrator 0 0 1
When I ...
- 449
0
votes
1
answer
26
views
Custom formula is not working in google sheet
I'm trying to add strikethrough style when a checkbox is check, but unfortunately, only one cell is applying the formula. Please see my screenshot below.
Screenshot
- 103
0
votes
1
answer
24
views
Remove protection from a specific cell when contents are deleted
I am trying to remove the protection from a specific cell after its content is deleted and then change its background to white.
Here is the script:
function onEdit(e) {
var spreadsheet = ...
1
vote
0
answers
31
views
Google Sheets - pull multiple lines of data from Workbook1 based on A1 in Workbook2 [closed]
Ok. So I've been all over this looking for an answer to my problem. I found so much helpful info, but none of it quite solves my problem. So I am here to ask the experts myself.
I work for a ...
- 19
1
vote
0
answers
132
views
Display flag emojis in Google Sheets using Windows 10 [closed]
When I write a flag emoji like 🇨🇳 into a Google Sheets cell it displays as text and not as a flag when I am using Google Chrome or Microsoft Edge in Windows 10. Is there a way to display it as a ...
- 111
1
vote
1
answer
105
views
Google Sheets | How to copy/download a cell's raw decimal value
Copying a decimal value from a cell set to show only N decimal places of that value actually copies to your clipboard what's shown, not the raw value.
The same happens when downloading a sheet as a ...
- 11
1
vote
1
answer
25
views
Iterating over two arrays and comparing each pair
Essentially, I would like to iterate over two 2-dimensional arrays of cells, and compare each pair of cells via REGEXMATCH.
I briefly attempted nesting ARRAYFORMULA, however this didn't seem to give ...
- 11
0
votes
1
answer
73
views
Transpose range while excluding hidden columns in Google Sheets
How can I transpose a range while excluding hidden columns?
The transpose formula always includes hidden columns by default.
- 5,438
0
votes
2
answers
106
views
Google Sheets keyboard shortcut: 'Sort sheet AtoZ or ZtoA' on current column?
How can I make a Google Sheets keyboard shortcut for both Sort sheet A to Z and Sort sheet Z to A on the current column (if one does not already exist)?
I'm constantly GUI-header-clicking a pop-up ...
- 127
0
votes
1
answer
31
views
How do I return the value of a specific cell within a range on Google Sheets based on current date?
I have a spreadsheet that I'm using to track weekly performance metrics and want to create a summary page that only shows me the value of each metric for the current week.
The worksheet is currently ...
- 1
1
vote
1
answer
100
views
Trouble Google Script in Sheets to Print from A1 to cell that contains text "stop print". sheet.setPrintArea not a function- anything similar?
I've been working with both ChatGPT and Google Bard trying to learn how to do this.
It seems that sheet.setPrintArea is not a function nor does there seem to be one similar.
I did a search on ...
- 13
0
votes
1
answer
146
views
Automatically append text in cell to URL in Google Sheets
Suppose I have a Google spreadsheet with a column of codes which correspond to a URL suffix. In this case, I have papers on arXiv listed as follows:
arXiv
1712.03255
2303.14090
2012.02922
How ...
0
votes
1
answer
29
views
How do you add 2 leading zero to the first column and 1 leading zero to the second column in an array formula?
I have spreadsheet and I need a way to format the formula so that the first column have two leading zeros (000) and the second column to have one leading zero (00)
Minimum required code:
=ARRAYFORMULA(...
- 111
0
votes
0
answers
37
views
Auto fill in some of the sheet
First time I have been on her to ask anything, myself and other staff are having difficulty with this one.
We work in a school and are trying to create a "Truancy tracker" on Google Sheets.
...
- 9
1
vote
1
answer
89
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
0
votes
0
answers
25
views
Count files in a Google Drive folder in Google Sheets
I am managing a construction project where we have to replace 6500 lead lines with copper from curb to inside meter. I have inspectors getting the right of entry and preconstruction pictures daily for ...
2
votes
1
answer
30
views
IMPORTRANGE and INDIRECT combination error
I am trying to dynamically import data from another Google sheet (sheet2), to current Google sheet (sheet 1).
In sheet 1, I would like to input the starting cell A2 and ending cell D20 in cells B2 and ...
- 23
1
vote
1
answer
89
views
Google Sheet ImportRange embedded in filter with RegexMatch option
I have a complicated spreadsheet with multiple tabs that filter for various things. The raw data comes from a file that I must download as a .csv file each day. Looking for a way to simplify a bit.
...
1
vote
2
answers
35
views
Is it possible to do a more concise IF x = 1 OR x = 2 OR x = 3 in Google Sheets?
The following is a part of a formula I'm using to filter for week day rows (for conditional formatting):
OR(
MOD(ROW(),7)=2,
MOD(ROW(),7)=3,
MOD(ROW(),7)=4,
MOD(ROW(...
- 416
-1
votes
2
answers
58
views
Conditional formating based on calculated cells [closed]
I have a sheet a sheet which records/calculates achieved Foreign Exchange rates.
There are 3 columns - € Rate; £ Used and € Got. The second two columns are given values according to the activity and ...
- 541
0
votes
0
answers
20
views
Google Sheets - Formula for If Cell A is exactly Option X (from a dropdown menu) then Link to Cell B
I'm trying to write a formula that will paste a link on Cell A (ex: G5) to Cell C (U5 on the same spreadsheet) if Cell B (H5) is exactly Option X (ex: "General Event") from a dropdown menu ...
0
votes
1
answer
30
views
Formula to check is cells are blank. If the cell is not blank then assign a value
I have a data set on Google Sheets where for example,
Name
Stage 1 Date
Stage 2 Date
Stage 3 Date
Stage 4 Date
Bob
3/1/2023
3/5/2023
3/31/2023
4/15/2023
Joe
2/1/2023
Zoe
3/21/2023
4/1/2023
4/15/...
- 19
0
votes
1
answer
23
views
How to use SUMIF to add values by unique Reference ID?
I am trying to write a formula that adds cell values by matching the reference ID in a separate column. For example, cells G2 and G3 have the same ID, so the amounts in cell H2 and H3 would be added ...
- 1
1
vote
1
answer
117
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
0
votes
1
answer
103
views
How to add custom autocomplete in Google Sheets?
I have a spreadsheet which is a dictionary mapping fantasy words to english definitions/words. I would like to be able to type the english word and get a list of matching fantasy words, when typing ...
- 235
1
vote
1
answer
111
views
function no longer working on mobile
I have a function that uses the onEdit trigger. It was working great both on PC and mobile yesterday, but today it no longer works on mobile but still functions on my PC. Not sure what is going on.
...
- 11
0
votes
1
answer
246
views
can i name a range using multiple columns that are all non adjacent to eachother
So I have data in columns that are non adjacent and would like to treat them as one named range is that possible the columns are highlighted in blue and are as follows b3:b25,l3:l25,v3:v25
https://...
- 99
1
vote
1
answer
70
views
AppendRow Column Offset
I wrote a Google Sheets Apps Script to function as a data entry form for my team to use. The script works great, but I'm looking for a way to improve it. I'd like to be able to skip the first column ...
- 13
0
votes
1
answer
1k
views
How to put multiple hyperlinks inside a Google Sheets cell
I tried multiple option, all returned me an error.
When I want to put one link in a cell this works fine :
=HYPERLINK("mylink.com";"link1")
Here is what I tried for multiple links (...
- 103
0
votes
1
answer
144
views
How to use OFFSET in ARRAYFUNCTION?
I have the following function, which calculates the average of the previous 7 rows of the column on the left.
=AVERAGE(OFFSET(C8;-6;-1;7))
The problem: whenever I update the data column I have to ...
- 147
2
votes
1
answer
156
views
data validation case sensitive string in google sheets
I'm trying to formulate a data validation code in Google Sheets for a simple 6-letter string - any 3 capital letters and any 3 lowercase letters. I believe I should be using the RegexMatch function. ...
- 21
1
vote
1
answer
100
views
How do you work with the Color Class array?
In Google Sheets using Apps Script, I want to read in the font color in many cells, change some values (change colors) in the array, and write them back to the cells.
I have previously used ...
- 11
0
votes
2
answers
32
views
Automatic Numerical Incrementation [closed]
I'm trying to get automatic incrementation working for a dynamic range of data. I've got this convoluted formula that works in some of the cells but not others, returning a #Value error that doesn't ...
- 11
3
votes
3
answers
127
views
Google Sheets - smart filtering
So I need to do something which might be a standard feature in spreadsheets, but I have no idea how.
I have a landing page that processes T-shirt purchases by families. Each row represents an order, ...
- 133
0
votes
2
answers
31
views
IMPORTHTML in several columns and SUM
I want to calculate the average historical dividend yield of three stocks. For this I import the data from a table with IMPORTHTML.
When I now calculate the sum, the value is 0.
I have played around ...
0
votes
2
answers
328
views
What's the workaround to copy the link for a cell when using IMPORTRANGE?
I want to know what the correct way is to use IMPORTRANGE while it also gets the correct link for another spreadsheet. What ends up happening is that the link directs to the current file instead of ...
- 101