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.

Filter by
Sorted by
Tagged with
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 ...
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 ...
user avatar
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....
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 ...
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 ...
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 ...
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 ...
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/...
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 ...
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?...
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:...

1
2 3 4 5
172