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,595
questions
0
votes
2
answers
20
views
Find a cell containing text in a range and return the text in that cell
I have a range of data where each row is in alphabetical order and I want to pull each cell in the row that contains "client" to another column.
- 1
0
votes
0
answers
9
views
How do you import cell fill color from another spreadsheet? [duplicate]
I am currently working on a spreadsheet with 100+ conditional formatting rules on columns, and the formatting rules significantly reduce initial page load speed. I already implemented IMPORTRANGE(), ...
0
votes
1
answer
11
views
Google Sheets - Auto group and sort
I've got a bunch of data, in this instance it's lists of fixtures for my son's juniors football team, which I'd like to order for ease. The age group is split into sub teams, e.g. U10 Reds, U10 Blues ...
- 1
0
votes
2
answers
16
views
Excel style table in Google Sheets
In Excel, I can create a table (n.b., not a Pivot Table) through Insert > Table with headers where I can sort and filter the table among other functions.
Is there a similar functionality in Google ...
- 101
0
votes
0
answers
18
views
Is there a way to have Google Sheets automatically update a formula when a new row is added? [closed]
I am currently working with the following spreadsheet:
Spreadsheet image
The write rows under the dealer names is where information will be entered concerning deliveries made and the columns to the ...
Tom M
0
votes
1
answer
12
views
Displaying Indian Number system (Lakhs and Crores) in Google Sheets
I want a custom formula for Google Sheets to format both crores and lakhs in Indian Number System. I have the custom formula for positive, but couldn't figure out how to add negative number system in ...
-1
votes
2
answers
17
views
Is it possible to select a column of dates in Google Sheets and change just the month?
I'm working on a spreadsheet with a different sheet for each month, and while I'm duplicating the sample data realized it would be easier to just select a column and change the month without changing ...
0
votes
0
answers
12
views
Integrating API on a Google spreadsheet to find VAT information [closed]
I am struggling to understand how to exploit the API from OpenAPI in Google Sheets. Specifically, I have a list of circa 2500 companies reporting only their VAT values, and I would like to exploit the ...
- 1
0
votes
1
answer
22
views
How to clear all 0 values from range except for one 0 value preceding data?
I have a table in a Google sheet that looks like this:
The data updates daily with a new column added after Column "Date" and is intended to track how quickly my team moves through projects....
- 13
0
votes
1
answer
16
views
Formula to select certain people only
I have a spresdsheet in google sheets and use the following formula
=QUERY('Main Data'!A2:U,"select B, sum(J), sum(E),count(E), sum(F), count(F), sum(G), count(U),
WHERE A IS NOT NULL
AND O>=...
- 1
1
vote
3
answers
51
views
Repeat a sequence N times
I have this table
Name (Col A)
Start Date (Col B)
End Date (Col C)
Name_1
1/31/2023
2/2/2023
Name_2
1/16/2023
1/17/2023
and I am trying to change it to a long format, like this:
Name
Date
Name_1
...
- 25
-1
votes
0
answers
20
views
countif with AND/OR?
Let's say I have a running tally of responses from parents of kids.
I want to keep track of which kids' parents have responded A or B, but obviously do not want to double count if both parents have ...
- 1
0
votes
0
answers
10
views
Googlefinance historic currency exchange rate wrong
If in google sheets I do
=GOOGLEFINANCE("Currency:EURIDR")*GOOGLEFINANCE("Currency:IDREUR")
I get a value close to 1 as expected. (Product of exchange rate and reverse rate should ...
- 101
0
votes
1
answer
26
views
Check What Same Text Is Found In A List & List The Text
I have 2 columns of values. I want to create a formula that looks at Column A and finds what Column B text values are within in the Column A items. Then I want what matches to list in column C. I've ...
0
votes
1
answer
40
views
Choose top 5 scores from multiple columns with conditions
I am trying to automate a bowling statistics table. Each bowler has multiple games, one column for each.
The problem I am having is that I am using =J1>=LARGE($J$3:$K$22, 4) in conditional ...
0
votes
2
answers
23
views
Google Sheets: Arrays: using ARRAYFORMULA() v. { }
Why does this work: =ARRAYFORMULA(LEFT(A2:A13,1)),
but this doesn't: ={LEFT(A2:A13,1)}
ie, using ARRAYFORMULA returns the array: J
F
M
A
M
J
J
A
S
O
N
D, while using the braces instead of the formula, ...
- 21
0
votes
1
answer
29
views
Best way to SUM all the columns between two particular columns
What should be the best approach to get a sum of cells that are between two columns A and E. Column A has a header of "Month Jan" and col E has a header of "Month Feb".
Columns are ...
- 3
1
vote
3
answers
38
views
Extracting non repeating data
Hi,
is there a way to ignore the duplicated values in columns A and B (highlighted) and extract ones that don't repeat (column C extracted manually for example)?
- 13
-2
votes
0
answers
35
views
Countdown timer in google sheets
I need to create a countdown timer which will be added to a spreadsheet that will show users how long they have left of their break times.
The user should be able to enter their overall daily break ...
-1
votes
1
answer
24
views
How to resize column to width of selected cell?
When I double-click the header of a column (its right border), the column will be resized according to the largest width of all its cells.
How can I do that resize instead according to the width of ...
- 5,823
0
votes
1
answer
17
views
Sorting data extracted by QUERY by a new column in the destination list
I am pulling some filtered data out of a master list by QUERY. I am also adding another column, which does not exist in the master list, to the date in the destination list. My question is how should ...
0
votes
1
answer
20
views
Pulling all data from a column
tl:dr - I have a code that can work but want to make it better by pulling everything from column E into 1 message (including header)
I am trying to get the output of a singular column to post to ...
0
votes
1
answer
27
views
Google Sheets - merge text in Apps script
i am looking for solution to merge text fragements in Apps script to automate a process.
I have 3 sheets (sheet_1, sheet_2,sheet_3). In each of the sheets i have 10 cells (A1:A10) to be filled by the ...
0
votes
1
answer
42
views
How can I import json from url using only existing formulas of google sheets (without scripts or apps or whatever else additional there is)?
I want to get data from this url:
https://api.monobank.ua/bank/currency
It has currency exchange rate data. But it's json. So whenever I'm trying to use IMPORTXML or IMPORTDATA it gives me error ...
0
votes
0
answers
16
views
Script for creating an xml file to be updated froom Google Sheet [closed]
Good day
In general, there is such a table in Google Sheet https://docs.google.com/spreadsheets/d/1tVs7IvRxyiDjwIpt4sKjMfkKsZymJU0BuiGMSFCFwWU/edit?usp=sharing
From this table, I need to generate such ...
- 9
2
votes
2
answers
112
views
Creating a custom menu in google sheets
I'm currently using the below code to create a custom menu called 'Team Admin' in Google sheets which allows a user to activate the function 'newSheet' when the menu item 'Add Agents' is clicked.
...
0
votes
1
answer
72
views
How to integrate the real OpenAI ChatGPT with Google Sheets?
Nowadays there are a lot of posts using the word ChatGPT but many of them use it as a common noun to refer to GPT-3 and some chatbots, not to specically make refence to https://chat.openai.com/chat / ...
- 42.7k
0
votes
1
answer
22
views
Formula to randomly select the name associated with lowest value from data set & a random from the lowest options if tied with drop-down select filter
I have a data set on Google Sheets where for example,
Name
Scores
Region
State
First
Row
Row
Row
Second
Row
Row
Row
On another tab, I have a drop-down list to select a specific Region and State. I ...
- 3
0
votes
1
answer
16
views
Expanding / collapsing groups in Google sheets without affecting other users views
Can anyone help? I have a huge sheet with lots of filters and groups. Does anyone know if a way I can great views with certain groups expanded and others collasped that doesnt affect what other users ...
0
votes
1
answer
21
views
VLookup, Index Match, Lookup not working correctly
I thought I was doing the formula correctly using index-match but it's giving the wrong answer. I also tried a VLOOKUP, MATCH, and LOOKUP and still not getting the correct answer. I have a range ...
1
vote
2
answers
26
views
How to sort unique cells in order of appearance on google sheets?
So, I know there is a easy way to sort unique values in alphabetic order using
SORT(UNIQUE(),1,TRUE)
but i'm trying to order based on how many times a item has appeared.
exemple, if had a column like
...
- 13
0
votes
0
answers
31
views
Update Google Sheet Column A + B with new data without losing Column C - F data
I have a document tracking the file size of each project on my server (Column A + B) and important information about each project (Columns C - F).
I need to update the project size each month and ...
0
votes
1
answer
15
views
Using a Ranking in one column to lookup names in another column and put in rank order in a 3rd column
I am trying to create a formula that will use a ranking (K2:K11) or the name of the ranking (L2:L11) and then match it to the respective name from A2:A11. The problem I am having is I am not sure how ...
0
votes
1
answer
16
views
Are named functions able to be shared?
I created a custom named function in Google sheets to simplify a really long formula. If I share this file, will the named function still work for the other user?
- 177
0
votes
0
answers
16
views
Array formula & concatenate
i want to make 6 number id code with array formula by combining the job code and the number list... 2 first number is the job code and 4 last number is the number list..
I tried to divide the job code ...
-1
votes
1
answer
20
views
How to refresh a single cell in google sheet
I wanted a solution for single cell refresh in google sheets.
Searched in here and got an excellent solution.
" 1. Select the cell you want updated.
2. Delete the cell.
3. Undo with Edit > ...
1
vote
1
answer
22
views
Automatically record daily values from a column in a new row
I currently have a project tracker in a google sheet which records a project name, status character column and three progress columns as below:
I would like to record a daily log of each progress ...
- 13
0
votes
2
answers
18
views
How to sum based on criteria from two columns?
I want to sum data from one column based on criteria met from two other columns. I tried the following, but I'm getting an error: "Wrong number of arguments to SUMIF. Expected between 2 and 3 ...
- 1
0
votes
0
answers
20
views
How to execute text to formula and return the value?
I need help with
I want G2 to return the calculated value which has to be 2250.977437.
- 1
0
votes
1
answer
22
views
Search a list for current sheet then show specific cell in that sheet
I've been using the below formula which is supposed to search a list of tab names in range AK17:AK32 and then if the user is on that current tab it will then show the corresponding result of the cell ...
0
votes
1
answer
32
views
How to sort a range alternating Male Female
I have a sheet for my classes, containing a seating chart and a list of student names. With data sorting I am able to generate quickly a new random seating chart, but I would like to also do this ...
2
votes
1
answer
51
views
How do I make the weight column in my medical log sheet get updated with the most recent weigh-in?
I am looking to get the weight column in my medical log sheet to be updated automatically when I key in the new weights on my Weight Log sheet. I take the weights weekly and update the info into a new ...
- 23
0
votes
1
answer
20
views
IMPORTRANGE to refer the function but not the value/result
Thanks for the help
Here is my situation
There are 5 different spreadsheets, i.e. Master, 1, 2, 3, 4)
All cell A1 in 5 different spreadsheet has the same function e.g. =count(B1:B5), they count the ...
- 1
1
vote
1
answer
16
views
How to nicely import CSV contacts to Google Contacts preserving as much information as possible?
I was looking at the help file for importing contacts to Google and it didn't specify how to organize the columns:
Company First Name Last Name Email Phone Mobile Street City State Zip ...
- 684
0
votes
2
answers
32
views
Can you do an importrange on data from multiple sheets if the data itself has an arrayformula with a textjoin?
I'm trying to summarize a bunch of sheets into one via a query([(importrange)(importrange)(etc.)}. I've done this multiple times without issue, but now the data I'm pulling is has a columns that is ...
- 1
0
votes
1
answer
9
views
Copying Google sheets data to newly inserted rows (which have been inserted by a script)
We would like to extend an (amazing) script for Google Sheets that inserts multiple new rows in Google Sheets based on a cell value (original question and answer: https://webapps.stackexchange.com/a/...
- 15
0
votes
0
answers
16
views
Using Apache Superset and Google Sheets, how to check that the value is not null, as well as add other conditions? [migrated]
I'm using Apache Superset to retrieve data from Google Sheets. Shillelagh is used for integration with Google Sheets.
I have a column which specifies a deadline for tasks. If the task doesn't have a ...
- 3
0
votes
2
answers
22
views
ROUND inside COUNTIF in Google Sheet
Is it possible to use an operation on a range inside COUNTIF? Let's say I want to count all the occurrences where a range is less than 50 after rounding. Can I do that without creating an extra column?...
- 155
1
vote
2
answers
41
views
Query+Importrange for multiple sheets based on values in Col26
=QUERY({
importrange("1HGW2VJFyhosCM-5AK7II6CRt19sPMsmGPbxEJvPVnK0","SUN!A3:AB5001");
importrange("1Fjbl3Ndh-thsb9ufNhJ2GgHjjIfvG1e7vUe4dNcwCew","SEA!A3:...
0
votes
3
answers
19
views
Filter formula: Exclude data without a timestamp
=sort(
filter(
{ Bryson!A4:S502; David!A4:S502; Justin!A4:S502; Michael!A4:S502; Manny!A4:S502; ScottC!A4:S502; ScottD!A4:S502; Zhenqian!A4:S502; Visitors!A4:S502 },
regexmatch({ Bryson!Q4:...