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

I need to equalize a text phrase with a numeric value

So what I need is to make a neat-looking salary-counting spreadsheet, which doesn't show the numeric value in its cells but instead text values. To be specific, my case is I'm a teacher and I have ...
Big Smoke's user avatar
1 vote
1 answer
25 views

Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col1

I have a Google Sheet, that I am using as a tracker for a game. This was not written by me, just passed down, and I am having a hard time trying to decipher the issue here. As per the title of the ...
Roland410's user avatar
0 votes
1 answer
21 views

How do i utilize IF functions (or others) in order to create a simple username/password in google sheets?

On cell A3, there is a username input (i.e max or dave), on cell B3, there is a pin input (i.e 1234 if its max, or 8640 if its dave), how would you go about creating a formula in order to satisfy ...
ZenLife18's user avatar
0 votes
0 answers
30 views

Why am I being blocked from using the Google Analytics Add-on In Google Sheets?

I'm trying to add the Google Analytics Add-on from within a Google Sheet. However, I'm being blocked. Here are the steps I'm taking: Click "Extensions", Hover over "Add-Ons", ...
user avatar
2 votes
1 answer
18 views

Is it possible to have a pivot table automatically incorporate newly added 'Filter by' values or show the current values?

I've made a test sheet here (https://docs.google.com/spreadsheets/d/17l49Rn3ZbNx-upzBtmRtsJBHQiCKXuYPs8SMTyrcw9k/edit#gid=0) You'll notice that the pivot table IS correctly pulling in ALL the data in ...
james's user avatar
  • 123
1 vote
1 answer
19 views

Script Failing to Finish - Protected Sheet

I've been using a timed trigger with this script to add new columns daily to a school-wide tracking sheet. Recently, I've been getting notifications that the script is failing on only one of the ...
Lauren Andrews's user avatar
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,...
Huge's user avatar
  • 11
0 votes
2 answers
28 views

Google Sheets QUERY to return if contains multiple selection and all other values

I have a data set similar to the image in cells A5:D31. I am trying to write a QUERY function that will return the records that match the "filters" in range A2:D3. In this example, the query ...
Elaine's user avatar
  • 19
0 votes
1 answer
26 views

How to make arrayformula automatically applied to newly inserted rows?

The following arrayformula needs to be applied to the entire column B, so that it populates Twitter account IDs in the cells of Column B according to hyperlinks in Column A. It firstly checks whether ...
Ian Y.'s user avatar
  • 113
0 votes
2 answers
29 views

Identify first appearance of data in an unsorted list in Google Sheets

I have a funky lookup situation, consider this example data Kris | Apr-2022 | ? | Kris | May-2023 | ? | Kris | Jun-2023 | ? | Pat | May-2023 | ? | Pat | Jun-2022 | ? ...
james's user avatar
  • 123
1 vote
1 answer
17 views

Trigger script to share calender with new user whenever form updates sheet with a new email address

I have a Google form that allows people to submit their name and email address in order to sign up for a private shared calendar. The form populates a Google sheet. I want to automatically add new ...
knawhatimean's user avatar
-1 votes
1 answer
59 views

Choose an employee and show the days where he has a specific shift code , along with the employees with the same shift

For convenience, Let's assume we have a shift schedule of a week. Header has the days from Monday to Sunday, and the 1st column contains the names of the employees. Names Monday Tuesday Wednesday ...
Maidenas's user avatar
-1 votes
2 answers
52 views

Formula that calculates the difference between numbers

I'm trying to create a formula that would be able to calculate the difference between the numbers and writes it exactly as shown in this image: https://i.postimg.cc/W4cZDgD2/image.png Full Example: ...
Ellie's user avatar
  • 1
2 votes
1 answer
34 views

How to do autoresizecolumns() in a Google Sheets file when data imported from another Google Sheets file is changed

I'm new to Google Sheets and apps scripts. This is my first ever question. I apologize in advance if I don't get it quite right. Google Sheets file Staff Roster sheet Roster contains names, contact ...
Marshall Morrise's user avatar
0 votes
0 answers
16 views

Creating a Meal Planner on Google Sheets - some help needed [closed]

I want to create a file that will allow me to track my diet. The idea is that the first tab will be for choosing the daily menu dynamically. Each additional tab in the sheet will contain a specific ...
Shay's user avatar
  • 1
1 vote
0 answers
19 views

Insert a new row in Sheet 2 for each cell in a range in Sheet 1 that is not empty [closed]

Hi I'm new to the Google Sheets App Script and not sure where to start. I'm trying to write a Script that will insert a new row in Sheet 2 for each cell in a range in Sheet 1 that is not empty. For ...
newbie's user avatar
  • 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 ...
Tom James's user avatar
0 votes
0 answers
11 views

I am trying to import data from this link https://nextgenstats.nfl.com/stats/rushing#yards into a google sheet [closed]

I legit cannot find a way to pull the table into a google sheet. It is dynamic and it is legit so hard to pull. Please help. Step by step would be very appreciated.
Aman Solanki's user avatar
0 votes
1 answer
13 views

How search for multiple values in string and return a value depending on the match?

This is my sample data A ... 1 This doesn't match ... 2 This one has Foo ... 3 In this, Foo exists too ... 4 Here we have Bar this time ... I have in a different sheet the values to search A 1 ...
DSantiagoBC's user avatar
0 votes
2 answers
54 views

Two custom fnctions triggered by OnEdit both returning errors

I have two custom functions bound to a Google spreadsheet with an onEdit() simple trigger that are returning errors. The first function sortStatus() sorts the columns in range A2:R by I2:I (col 9) ...
rellawp's user avatar
0 votes
0 answers
13 views

How can I set up Google Sheets to automatically copy and name a template tab daily? [closed]

Is there a way I can set up Google Sheets to automatically copy and name a tab on a daily or weekly basis?
Vicky Cox's user avatar
0 votes
2 answers
47 views

Extraction social hashtags with diacritics from a cell?

I´m with a similar problem, like this post: How can social hashtags be extracted from a cell? Basicly it asks what to do to extract hashtags from a cell in google sheets. Some good answers, like this ...
Leandro Medeiros de Souza's user avatar
1 vote
2 answers
33 views

Count the rows in one range based on both a key and the existence of a value in another range

This is likely a duplicate, but it's difficult to search for or to recognize when I have found it. In Google Sheets, I want a formula for E1:E4 (pink) that will give me the count of rows in A1:B5 (...
Corey's user avatar
  • 152
0 votes
1 answer
19 views

How do I use REGEXTRACT to return grouped numbers from a string where the number of groups varies?

In Google Sheets, I'm trying to return numbers from within a text string. The numbers are values of the property named either item_id or item_ids. I've tried the REGEXTRACT function but I'm struggling....
user319422's user avatar
0 votes
1 answer
28 views

SUM cost if Product ID is unique and matches multiple criteria

I have data in a sheet similar to the image. There are rows with duplicate Product ID information and I want to calculate the total cost of a unique Product that matches multiple criteria. In this ...
Elaine's user avatar
  • 19
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 ...
Charlie's user avatar
  • 11
1 vote
1 answer
29 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 ...
Snoymonkey's user avatar
0 votes
2 answers
25 views

Google Sheet: Extract name from emails using a list of names to compare

My list of subscribers, the majority of them have not enter a name but in their emails 1 from 10 has name in it. For example [email protected] "Rose" is the deducted name. So I will like to ...
user avatar
0 votes
2 answers
29 views

COUNTIFS not working :(

Anyone able to tell me why this generates 0 and not 5 please? My COUNTIF works fine below, but selecting multiple criteria doesn't seem to... Probably some simple syntax error..?
Stu Bridge's user avatar
0 votes
2 answers
22 views

Google Sheets - COUNTIFS with some blank values and multiple criteria

This seems like it should be easy but I don't know what I am missing. Example Data: Names Numbers Dave 12.00 John 14.30 Tom 11.40 John John 21.11 John 23.00 What I am after: ...
Jason A's user avatar
0 votes
1 answer
27 views

How Can I Force INDIRECT to Update When I Add a New Sheet?

I am using INDIRECT to reference sheets that I will create in the future (Week 1, Week 2, Week 3...) I use IFERROR to hide the errors in the cells for future weeks where the sheet does not exist yet. ...
Phil Anderson's user avatar
0 votes
0 answers
10 views

Bio assignment graph

I'm working on an assignment for a bio class that I'm taking, and this assignment requires me to create a graph. I have the graph and the information entered, but have no idea how to manipulate the ...
C. D. P.'s user avatar
2 votes
1 answer
35 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
0 votes
1 answer
21 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
-1 votes
1 answer
28 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
  • 9
2 votes
1 answer
167 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
  • 257
0 votes
1 answer
39 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
  • 9
0 votes
1 answer
29 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
0 votes
1 answer
21 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
0 votes
1 answer
15 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
  • 15
1 vote
0 answers
23 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
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/...
Сильный Могучий's user avatar
0 votes
1 answer
24 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
21 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
41 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
0 votes
1 answer
34 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
0 votes
1 answer
43 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
  • 3
1 vote
1 answer
42 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
21 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
35 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

1
2 3 4 5
175