Skip to main content

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.

1,777 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2 votes
1 answer
52 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
2 votes
1 answer
152 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
2 votes
1 answer
91 views

How can I retrieve a 2D array by its header?

What I am trying to accomplish here is to build a spreadsheet for meal planning. On the left side, with the column header called dish ideas, I am logging all the individual ingredients of a recipe. On ...
rwx131's user avatar
  • 21
2 votes
1 answer
695 views

How do I change the number format for the horizontal axis labels in a histogram?

I would like to change the number format of the horizontal axis labels of a histogram. Some tutorials mention that this should work, e.g. New chart text and number formatting options in Sheets How To☝...
FlorianL's user avatar
  • 121
2 votes
2 answers
818 views

How can I group/count occurences of a column

I have a Google sheet with 2 columns. | Jim | Foo | | John | Bar | | Jane | X | | Jim | YZ | What I usually do is: download the values as a CSV write/run a script that processes the CSV the ...
Jim's user avatar
  • 121
2 votes
1 answer
62 views

Recursively search cell for each value within an array and return position within array

I have a bunch of transaction descriptions, and I'd like to check against an array for any matches (case insensitive, in any position within the cell) and return the associated position within the ...
Ellipsis's user avatar
  • 121
2 votes
1 answer
597 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. ...
Chris Mcghee's user avatar
2 votes
1 answer
259 views

Split Text to columns in Google Sheets with multiple full words as delimiters - "to" or "for"

I need to split a text when the delimiter is "on" or "to" or even "for", removing the delimiter from the results: Ex: Creating more value for business = Creating more ...
Carol's user avatar
  • 21
2 votes
1 answer
3k views

Showing rounded integer values in a Pie Chart in Google Sheets without decimals (Pie Chart - Slicing Label does not work)

The proposed solution for the general question has been already asked here: How to change the values of a pie chart to absolute values instead of percentages in Google Sheets But it doesn't work for ...
chelder's user avatar
  • 371
2 votes
1 answer
131 views

Using Google Sheets to bulk collect the number of Google Search results count for given keywords

I need to bulk check the number of Google Search results count for given keywords by using Google Sheets or Microsoft excel For example, ocean is About 2,960,000,000 results
Tasha Williams's user avatar
2 votes
1 answer
211 views

Google Sheets - Button link to script with a range input

Is there a way to write a script, assign it to a button, and press the button to execute the script AFTER selection a cell range that serves as an input parameter to the script in question?
Jeff's user avatar
  • 21
2 votes
1 answer
302 views

Creating a rolling date formula to automate spreadsheet

I'm not very experienced with google sheets formulas, and I'm attempting to create a rolling year formula for a scholarship spreadsheet. All I really need it to do (if it's even possible) is to ...
Tatiana's user avatar
  • 21
2 votes
2 answers
208 views

Can I avoid copying this formula in new rows by using ARRAYFORMULA

Suppose I have this sheet showing which group (1, 2 or 3) each student joined on each day of class. Column B indicates in which group he/she last showed up, which is obtained by using the following ...
jmichel's user avatar
  • 43
2 votes
0 answers
35 views

How to link 2 sheets, so when a cell on sheet1 is modified, sheet2 got updated automatically and viceversa

I am trying to have the same updated values in two differents sheets of google spreadsheet. So when I edit sheet1, the the values in sheet2 got updated. Also the opposite: when I edit something in ...
Mario Márquez's user avatar
2 votes
1 answer
326 views

countuniqueifs one condition and not-another condition

In my sheet, Column G will either indicate "LIKE Submission" or "Incident Referral." Columns D-E-F list different names (one per row). I want to tally the number of unique names ...
gonzalo2000's user avatar
2 votes
1 answer
178 views

Query Formula and Multiple Columns

I tried to use Query function and it was functioning well at first. I initially only had 7 columns. However, when I tried to use to my document with up Column AZ, it is not working anymore. This was ...
Vera's user avatar
  • 21
2 votes
1 answer
23 views

i'm not sure what function I want for this

I have a column that holds the month number(1....60) I want in another column to indicate what the number "1" is (in this case January 2023) I need a formula to look at a column, if the ...
Michael Feiock's user avatar
2 votes
1 answer
81 views

NETWORKDAYS BUG?

It was fine yesterday~ It was fine in the past few years The whole company was in trouble this morning Simple NETWORKDAYS function, when counting the day to the current day, there is not a 1 but a 0 ...
2 votes
0 answers
1k views

Google Sheets / Maps - Exception: Service invoked too many times for one day

I have the following script in my Google spreadsheet to calculate the distance in miles and time driving between two addresses. It works perfect until I get an error "Exception: Service invoked ...
Joe Wright's user avatar
2 votes
1 answer
79 views

can you track how long a cell has been the same in google sheets?

I am making a filtered list of trade proposals in this google sheet. I would like to remove those that have been put online in the last 5-10 minutes and only show older ones. I would like to avoid ...
Postlim Fort's user avatar
2 votes
0 answers
54 views

Google Sheets: shared 'Make a Copy' link but still requesting access to master copy

I have been sharing spreadsheet templates on Google Sheets via links which instruct the users to make a copy, so they are able to fully access and edit their own version (by changing 'edit' to 'copy' ...
Adele's user avatar
  • 21
2 votes
0 answers
186 views

How to count cells and blank cells after up until another cell in separate columns

The title is a bit confusing, but essentially I am creating a calendar/tracker where a user inputs values into the "Work" column and the spreadsheet should count those values and blank cells ...
cbcb's user avatar
  • 21
2 votes
1 answer
117 views

"Protected sheets and ranges" appears set up OK, but still can't edit

I am using a shared Google Sheets" that has 2 sheets "Data" and "Codes". Protection was set on certain cells in "Data" and I can edit those without protection. But ...
John Pankowicz's user avatar
2 votes
1 answer
19 views

Google sheet trend chart help

Is there a way to display this years data like it is in this chart but have a cut off so the line doesn't drop to 0? Please see attached image Many thanks
user299539's user avatar
2 votes
1 answer
46 views

How to assign cell values based on options from another list

I am sure there is a simple formula for this but I have not been able to find a solution anywhere. I am trying to fill a cell based on a list in a different tab. In my main sheet, each row has a ...
Jack's user avatar
  • 21
2 votes
2 answers
54 views

Simplify formula that checks student year level and mark to a letter grade

I am writing a Google Sheet that checks a student's year level, then assigns a letter grade based on their mark in an assessment. If there is no mark, it is designed to leave the cell blank. The ...
Blake Howieson's user avatar
2 votes
1 answer
61 views

How to import a JSON-style table [['a', 'b'], ['c', 'd']] into Google Sheets?

Let's say I have copied a JSON-style string like this: [['a', 'what\'s up'], ['c', 'd']] into the clipboard (for example copied from my browser Dev Console, after some JS code). How can I easily ...
Basj's user avatar
  • 576
2 votes
1 answer
37 views

Average formula in Google Sheets

I'm new to data analytics and have a table in front of me with unitstocks. The numbers are separated with a period after to mark, if its an 18.00 or a 120.00 for e.g. when I use the formula average on ...
fishmoney's user avatar
2 votes
0 answers
497 views

How can I plot points on a 1d number line in Google Sheets?

I'd like to create a 1-dimensional chart in Google Sheets. The chart effectively plots values on a number line like so: A B C D E |-------------O-------...
joelittlejohn's user avatar
2 votes
1 answer
1k views

Sum Duration with google sheet

How can i sum duration with google sheet? I need to sum this value 1:14:127 1:14:057 1:13:938 1:13:894 I set the cell with duration "mm,ss,SSS" and i try this formula =sum(a1:a4) and =TEXT(...
user2086295's user avatar
2 votes
0 answers
2k views

Modifying the source document for linked tables in Google Slides

I have Google Slides presentations I use as report templates with data in charts and tables linked to a Google Sheets document. In some cases I need to change the document that is the source for the ...
twalbaum's user avatar
  • 471
2 votes
3 answers
210 views

Google sheets random number generation help

I'm trying to generate random numbers in an interval, x number of times. Imagine rpg dice sintax, like: 3d6 means roll 3 times a dice of 6 sides. Or 5d100 means roll 5 dices of 100 sides. If I was to ...
Igor Gabriel's user avatar
2 votes
2 answers
3k views

Google Sheet: is it possible to make a chart's vertical axis' Min and Max values automatically adjust according to the values in the dataset?

I want to have my chart, which displays a range of data that updates periodically, to represent the changes across sampled values in a more zoomed-in form by having the minimum value of the vertical ...
TLSO's user avatar
  • 165
2 votes
0 answers
133 views

If there anyway i can check if the cell contains the result of a arrayformula()?

For example i have this in A1 : =arrayformula(B1:B100) If in A10 i accidentally enter a value (which will overlap with arrayformula result) , it will produce error in the A1 because of existing value....
andio's user avatar
  • 397
2 votes
1 answer
314 views

Weighted Average Grade Book Based on Conditional Dropdown

I am trying to create a grade book using Google Sheets that produces a weighted average based on three grade categories; exams=45%, participation=30%, and homework=25%. I would like to do this be ...
JOSEPH ELORRIAGA's user avatar
2 votes
1 answer
25 views

Compare imported range with existing range, and filling corresponded data to the fields

I am not sure how to import data to corresponding cells. query(IMPORTDATA("https://www.iftach.org/taxmatrix/charts/4Q2020.csv"), "Select Col4 where Col1 = '"&A4:A&"'&...
Andrew's user avatar
  • 21
2 votes
4 answers
891 views

IFNA() doesn't work with date format

I have this table : column F is my data and column H is to check if column F has N/A error. In H1 i use this formula : ifna(F1,"error") Seems to work fine. But in the 2nd row when it check ...
andio's user avatar
  • 397
2 votes
1 answer
672 views

How to combine formulatext() and arrayformula()?

I want to use formulatext() with arrayformula() : =arrayformula(formulatext(A1:A10)) But The arrayformula doesn't work. It should output 10 rows but it only returns a single row output. Is there ...
andio's user avatar
  • 397
2 votes
2 answers
1k views

Delete rows in Google Sheet after 30 days

I have a Google Form that populates a Google Sheet with rider registrations for a local cycling club. The sheet has a filter in place to display only the riders for the current day. This filter ...
Paul Taverner's user avatar
2 votes
1 answer
693 views

How can you use a Gmail script to attach more than one image at a time?

I'm trying to set up a Google Sheet with some data to email images. I have the code I found online working below to send a single image attachment but I would like to send more than one image at a ...
Brett's user avatar
  • 21
2 votes
1 answer
1k views

Google Apps Script MailApp Not Sending Email To Script Owner

I've set up a script to take information from a Google Form and email that information to multiple people using Google Apps Script. What it does not do, however, is email the owner of the script. I'...
Mike Wielgus's user avatar
2 votes
0 answers
271 views

Get the contents of a cell that is next to the maximum value in a certain range

I ran into a problem that I couldn't solve. Suppose we have a table: A B C D E F 20 SHOP PSU NAME POWER W/USD LINK PRICE 21 abc abc number number link number 22 abc abc number number link number ...
Eryk Jabłoński's user avatar
2 votes
1 answer
2k views

3D Formula Google Sheets - sum across sheets

I'm trying to script an equivalent to Excel 3D formula into Google sheets. I have a workbook with numerous worksheets and a master sheet that should sum up the data. In excel I would have used this 3D ...
Mady's user avatar
  • 21
2 votes
1 answer
33 views

Function to create a calendar for one year with gaps (date and days in separate cells)

Is it possible to have a function within a Google Docs spreadsheet which I can link to a button which will create a calendar in the following format: Number will be 1 - 365 for the days Date will be ...
Simsphonx's user avatar
2 votes
2 answers
3k views

Select Unique on 1 Column in Google Sheets - Distinct Doesn't

I'm trying to set up a kind of a bid thing, where the top 5 respondents receive a prize. I was hoping to use SQL to get the top 5, but I can't remove duplicate bidders and only take their top bid. I ...
Jonathan M's user avatar
2 votes
0 answers
154 views

How to Use Google Sheets' Macro to Copy Data from Volatile (randbetween) Cell

What I would like to be able to do is execute a macro that copies the data from a cell with a randbetween formula, and pastes that data (copy/paste values only) into another cell. When I attempt this ...
Abyssal Spark's user avatar
2 votes
2 answers
85 views

Check if a string appears within another cell, and return the longer of the 2

I am having a hard time thinking of an formula that can help me pull the data needed in this example: In the C1 cell, I would like to make a formula that does this: Check if any cells in the A ...
X Luong's user avatar
  • 21
2 votes
2 answers
298 views

Replace first negative value in a given range with 0 and return the remaining values unchanged

In Google Sheets, I have a list of values in column A. In column B I want to return the values from column A except I want to replace the first negative value with 0. If there are no negative values, ...
David Ilic's user avatar
2 votes
1 answer
84 views

Add '/' character in between a number (which should be formatted as a date) and turn it to a date

I have received a spreadsheet which has the dates formatted with only numbers without separators and I would like to transform them into a date format. For example, I would like to change the number ...
Joan MITJANS's user avatar
2 votes
1 answer
37 views

How do I write a formula while referencing another sheet in Google Sheets?

So I want to do a CountA on Column A and Column B in Sheet Two and divide Column A by Column B in Sheet One to get the percentage totals. Can I write all that in one line? =SUM(Sheet!((COUNTA(A2:A516)/...
Lizzie C's user avatar

1
2
3 4 5
36