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
2 votes
1 answer
109 views

chart select data: there are more items than can fit in the menu

I have time series data with at least 250 data points. I graph the data with a Sheets Line Chart. I'd like to add some data points with labels to the graph via the Add Select Data Point option. ...
The Buggser's user avatar
1 vote
1 answer
267 views

Excluding specific range in Google Sheets

I have a range formula on a Google Sheet that pulls the next future date out from a column containing nothing but dates (column C), starting from the 4th row: =MIN(FILTER(ARRAYFORMULA(C4:C),C4:C>...
Chris Douglas's user avatar
0 votes
1 answer
33 views

All Time Rolling Average based on string order with multiple conditions in the same column - Google Sheets

I'm trying to calculate the average $ amounts per category (X & Y) and their sub-categories (A,B,C,D,E) on a rolling basis based on past data that's on a string format. There are 2 types of ...
pfnanc's user avatar
  • 41
2 votes
1 answer
187 views

How to force uniqueness of cell in Google Spreadsheet with non-Latin text in cell?

I have Kannada text in my column and it's not working, but it worked fine with Latin text before. Kannada text is from a region near India. Here is my spreadsheet, there are too many rows to include ...
Lance's user avatar
  • 277
1 vote
1 answer
51 views

Rolling Average based on string order with multiple conditions in the same column in Google Sheets

I'm trying to calculate the average $ amounts per category (X & Y) and their sub-categories (A,B,C,D,E) on a rolling basis based on past data that's on a string format. There are 2 types of ...
pfnanc's user avatar
  • 41
0 votes
1 answer
63 views

Does Google Sheets have a function to insert X number of Y characters within a cell? [duplicate]

I currently have a spreadsheet which requires a variable number of line breaks within a cell based on input data from another cell. Long story short, I currently am using a brute force method to ...
G0vnah's user avatar
  • 3
1 vote
1 answer
27 views

Stuck at understanding how duplicate prevention formula works in Google Sheets

Many blog posts and educational sites tell us to use =countif($A$2:A2,A2)=1 in the selected range. However, they don't explain it. And for me it doesn't work and since I can't understand it I can't ...
Saeed Neamati's user avatar
1 vote
1 answer
36 views

Automatic way to select a range for a formula based on the date

I am creating a calculator where the user describes a list of tasks in Activities!B2:B, and in C2:C they rate 0-5. To separate each day, I've used a date for each task in A2:A (stylistically separated ...
Louis S's user avatar
  • 25
1 vote
0 answers
28 views

ArrayFormula Trouble pulling data for just a year or just last 15 days No understanding how to do [closed]

Google sheet to see my problem https://docs.google.com/spreadsheets/d/1e3sjPNDJ3crJVQIn9L-lKllXP0wvc9NC22cnjxCveEs/edit?usp=sharing See separate tab (help) See all Yellow Boxes........they have the ...
jim groninger's user avatar
1 vote
1 answer
75 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/...
Сильный Могучий's user avatar
0 votes
1 answer
56 views

Column Identifiers as Described in Query Language Reference (Version 0.7) not working

In the Query Language Reference document found at https://developers.google.com/chart/interactive/docs/querylanguage#Table_used_in_Examples it indicates the following as properties of data tables. &...
Philip Wilson's user avatar
0 votes
1 answer
35 views

Using created filter to filter specific value base on other cells value

I can't seem to figure this out. I am wonder if it is possible to create a filter for a column, and then filter that column based on another cells value. So I have created a filter for A column which ...
Linkinpark's user avatar
1 vote
1 answer
86 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/...
Havok's user avatar
  • 11
1 vote
1 answer
78 views

Timestamp on change of another range

I have a Planning sheet that I use to follow budgets that are reported every week. I'd like to have K7:18 stamped with a date when there is a change in the corresponding cell of I7:I18 range. I cannot ...
user318609's user avatar
1 vote
1 answer
90 views

How to find largest group of consecutive numbers

This is a complex ask, so I apologize if it's unclear and will be happy to clarify. I have a series of numbers (dice rolls, in this case), and I need a formula to do the following: Find groups of ...
pub13's user avatar
  • 13
1 vote
1 answer
99 views

Conditional formatting based on frequency

I have a range (A1:L200) that has only numerical values (0-37). I have a formula that shows the frequency that each value appears: =FREQUENCY(A1:L200, N1:N38) How can I use this formula or something ...
Andrew Wade's user avatar
0 votes
0 answers
41 views

Putting a custom function in same file as Apps Scripts [duplicate]

I'm sure I'm missing something very simple, but here is my problem. I have a spreadsheet with an apps script that has global variables that require authorization (due to openBy). I want to add a ...
E.B. Sandberg's user avatar
3 votes
3 answers
120 views

Hide 0 value in SUM calculations

I have a simple sheet with additions, it looks like this: B column is for the sum of the values on the right, and if there is no value on a row, it shows 0, but let's say I want it to say 'None' ...
Ogi's user avatar
  • 33
0 votes
0 answers
117 views

Google Sheet Filter (display rows) by condition consist of MULTIPLE texts [duplicate]

I'm beginner on Google Sheets. I know there is custom filter that can display rows by condition consist text (that search for specific word). But there is a way to display rows that consist only ...
Mateusz Arma's user avatar
-1 votes
1 answer
43 views

How to filter a given shift schedule table to extract the dates and the shift codes where 2 workers of your choice have the same shift?

Suppose we have a given shift schedule table like in the picture I would like to create a new worksheet in which i will choose 2 different names, and the result will be three rows: Date,Worker 1,...
Maidenas's user avatar
0 votes
1 answer
321 views

How to extract date and time in Google Sheets [closed]

I'm trying to extract the time and date from column A. In cell A1 for example is written: October 25, 2023 at 07:23PM In cell A2 for example is written: October 26, 2023 at 01:01AM I'm trying to ...
Dennis 's user avatar
1 vote
2 answers
91 views

How to filter a schedule to return a list of employees based on date and shift code?

In Google Sheets, I have an employee work shift scheduling sheet named Shifts2. Column A lists all employees. Row 1 is a list of dates in November. The remaining rows in columns B through AG contain ...
Maidenas's user avatar
0 votes
0 answers
14 views

Google Sheet Data and Pie Chart [duplicate]

I need to show on a pie chart the data in Column B as a % of data in Column A How can I do that? The chart calculates the data in Column A as a % of the total sum of column A.
Thomy's user avatar
  • 1
0 votes
0 answers
20 views

How to copy an image generated with IMAGE function [duplicate]

I am generating images through a web service into one column using the IMAGE function. When trying to copy the generated images through [Ctrl]+[C] and [Ctrl]+[V] it does not copy the images. Is there ...
Alex M's user avatar
  • 1
0 votes
0 answers
17 views

How to fill a matrix pattern using autofill? [duplicate]

In a google sheet, I have some numbers in column A and in row 1. I want to fill the matrix as follows: A B C 1 f(B1, A2) f(B2, A2) f(B3, A2) 2 f(B1, A3) f(B2, A3) f(B3, A3) 3 f(B1, A4) f(B2, A4) f(...
whoisit's user avatar
  • 101
2 votes
1 answer
139 views

Can't remove header from mathematically generated column in QUERY

I have the following formula in D1 of my Google spreadsheet: =TRANSPOSE(QUERY(B:B, "SELECT "&A1&"/Col1")) It produces the header quotient(5()) in D1 that I don't know how ...
Anton's user avatar
  • 123
1 vote
3 answers
44 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,...
Nick Marker's user avatar
0 votes
1 answer
866 views

I am using Google App Scripts SpreadSheet.openById(), but it assumes Sheet0 as active sheet?

I am trying to create a trigger to get id when there is a change in sheet selection which is working fine when each google sheet has its own google app script, but I want to create a google app script ...
Zaid Bhimala's user avatar
0 votes
0 answers
55 views

How can I return values in one cell based on a range of values in another cell? [duplicate]

I am creating a spreadsheet for a racing league and would like driver points to be automatically calculated based on finishing position. Each finishing position from 1st to 10th has an associated ...
user avatar
1 vote
1 answer
430 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....
Zaid Bhimala's user avatar
0 votes
0 answers
76 views

Google spreadsheets : display all decimals, but no trailing zeroes [duplicate]

I want a number format that achieves both these results in one go : 1.2 3.456789 If change the number of decimals I can either get : 1.2 3.5 OR : 1.200000 3.456789 Neither of which I do want.
Charles's user avatar
  • 123
1 vote
1 answer
161 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 ...
Varun's user avatar
  • 11
0 votes
1 answer
65 views

Does it help to close a Google Sheet when it's slowed down due to "Calculating formulas"?

It's known that "Calculating formulas" in Google Sheet disaster is a problem in general, but I have a specific question - does it help at all if I or other concurrent users exit the sheet or ...
LWC's user avatar
  • 1,228
0 votes
1 answer
65 views

Google Sheets dropdown options starting with a plus

I need a dropdown in my sheet with modifier options for a number. They are in the style of "+2/+1" and "+1/+1/+1" where it could be two numbers or three numbers, separated by a ...
Spedwards's user avatar
  • 215
1 vote
0 answers
36 views

Lookup a specific series of cells by typing in a number? [duplicate]

I help run an RP server on discord, and we use google sheets for many of our mechanics. A new mechanic I am looking to implement is a fun little thing where they can perform an action to be given a ...
Raayven's user avatar
  • 11
1 vote
1 answer
286 views

How to count wins, losses and ties based on multiple conditions

I'm very new to Google Sheets. I have a sheet where I record the outcomes of games played. Each row represents a single game Column A, Map, stores the name of the map that was played. Column D, ...
Lunar's user avatar
  • 13
1 vote
1 answer
334 views

Hide Google Form responses from view in Sheets until a certain condition is met

I'm interested in limiting access to Form responses in Sheets until after I've had a chance to review/approve each submission. I want the response to be viewable to those I've granted view permission ...
Dawn Campbell's user avatar
0 votes
1 answer
134 views

How to fix VLOOKUP when it returns the incorrect value from the bottom of the list?

In Google Sheets, I am having trouble with a VLOOKUP function. range argument A2:B contains text strings. search_key is in column C, example, C2 index argument is (column) 2, which is equivalent to ...
Ylahris's user avatar
  • 181
1 vote
1 answer
57 views

If a cell is left blank, how can I get the formula result be blank

I am currently looking for a little help with Google Sheets. I currently have three columns, B 'DATE' - C 'TIME' - D 'Dropdown list - BUY, Sell, Proforma' So far, I have managed to do what I needed, ...
Steve's user avatar
  • 11
0 votes
2 answers
73 views

Conditional formatting for wrong number of decimal places

I am creating a lab for students where they enter data into a spreadsheet that, using conditional values, will tell them if their values and precision are correct. For example: The sample volume is 22....
user avatar
1 vote
1 answer
26 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 ...
JEOB's user avatar
  • 11
-1 votes
2 answers
52 views

Google Apps Script for gradebook sharing: Create duplicates of current sheet, one per row of data, with importrange of the source and header rows

The goal is to create a workaround to the typical problem of not having per-row privileges. I want students to only see their row of the grading document. Typical solution is to share another sheet ...
whatsnewsisyphus's user avatar
0 votes
1 answer
469 views

Google Sheets ordering by dropdown choice

I'm completely new to sheets and am currently trying to set up a filter for a column. The column is set up to use dropdowns to pick a string. What I'm trying to do is order it by the text, but not ...
Adam's user avatar
  • 9
0 votes
1 answer
44 views

Return averageifs of durations formatted to display only minutes

I have a column of durations formatted as "hh:mm:ss". I use AVERAGEIFS to return the average of the durations that also match a particular date. For example, I have the following formula in ...
user avatar
1 vote
1 answer
43 views

Trouble using IMPORTDATA when text includes commas

Shopify publishes a text file containing their Product Taxonomy online at the following URL: https://help.shopify.com/txt/product_taxonomy/en.txt I've pasted the file contents into Google Sheets as a ...
YBB's user avatar
  • 77
0 votes
2 answers
523 views

Query function returning blank values instead of text in column with both text and numbers [duplicate]

I'm using this formula: =QUERY(A1:M;"SELECT D") COLUMN D contains rows with numbers and others with text strings, but the QUERY is only returning the numbers and the text strings are showing ...
Juan's user avatar
  • 11
0 votes
1 answer
175 views

Google Sheets Conditional Format to highlight specific pieces of text within a cell

Is it possible to condition format so that sheets will format specific pieces of text within a cell instead of applying formatting to the entire cell. Is this an option in sheets yet? e.g If cell ...
Genny's user avatar
  • 365
0 votes
1 answer
35 views

View several comments in one cell on Google Sheets after reopening closed comments

I added a new comment in Google Sheets, then reopened a closed commend in the same cell. When I click on the cell, I see only one of the two comments. How do I see the other one in place?
root's user avatar
  • 724
1 vote
1 answer
32 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....
alphex111's user avatar
1 vote
1 answer
629 views

How to protect drop down menus in google sheet?

I want to make a google sheet, where multiple users can select a value from a drop down menu, and not do anything else (like adding other values, deleting content etc.). I have made this solution with ...
Ulrik Schmidt's user avatar

1
3 4
5
6 7
178