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 a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.

1,588 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
13
votes
3answers
6k views

Statically positioned chart (ignore scroll)

I have a Google Sheets document with four columns of data. I have a chart on the right side. When you scroll down to see all the data, the chart scrolls up off the screen. Is there a way to make it ...
5
votes
5answers
7k views

How can I select all cells that contain specific text in Google Sheets?

I have a spreadsheet that contains 20,000 cells. I want to select all cells that contain "foobar". How can I do this?
5
votes
2answers
414 views

Odd Google Apps Script behavior

I have a Google Apps Script project that I have used for the past few months (successfully) and everything has worked just fine. A few weeks ago, I started getting the "Unable to load files" error ...
5
votes
2answers
4k views

Copy range from specific values to top of another sheet

I want to copy everything from the range "Start" to "End" to the top row of another sheet (Sheet2). Sheet1: The data between "Start" to "End" can be any number of rows. This is my current code. It ...
4
votes
1answer
490 views

Google Data Studio and Slack integration

How Can I connect my Google Data Studio reports, or parts of them with slack? I would like that each day appear in slack some report that I will customize in Google Data Studio. And if I want to do ...
4
votes
0answers
4k views

How do you paste without format by default in Google Sheets?

In Google Sheets you can hit Ctrl+Shift+V to paste without the formatting, but, is there a way to change it so that you can use Ctrl+V? Either by changing the Keystroke or forcing the rows or columns ...
4
votes
2answers
1k views

Jump to bottom of spreadsheet, blank (non-formula) row

I had a tall spreadsheet that I need to add data at every time it opens. Answers like Go to Bottom Cell have been helpful, but my quirk is that the first column contains formulas that return blank in ...
4
votes
1answer
1k views

How can I create a Google Spreadsheets whose name and cells auto-complete with cells completed on another sheet?

I'm using Google Spreadsheets to manage projects, and I'm not familiar with scripts or macros. What is: 1) I have a Gantt spreadsheet which lists all the ongoing and foreseen project as follows (by ...
3
votes
0answers
56 views

“You have reached your limit of named versions” when naming a version

I didn't realize this, but Google Sheets (and I'm guessing Google Docs) as well seems have a limit on the number of named versions you can save? The bigger issue is that I'm not able to find any ...
3
votes
2answers
432 views

Conditional Formatting in Google Sheets

The shorter question to spare you from reading more than you need to I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 ...
3
votes
1answer
120 views

How to automatically convert all Microsoft Office file attachments in Gmail to Google Docs?

My Gmail has thousands of emails. Each email will have several attachments as Microsoft Office files (.doc, .docx, .xls, .xlsx, .ppt, .pptx). Currently, I want to automatically convert all Microsoft ...
3
votes
3answers
214 views

Conditional formatting for columns, date & time specific?

I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this: 2018-11-01T20:16:52 I have tried multiple approaches to sorting ...
3
votes
1answer
4k views

Calculate Percentage with QUERY on Google Sheets

I have a Google Sheets database like this: Name | Value | Answer A | 100 | Y B | 200 | N C | 10 | Y A | 100 | N A | 1000 | Y B |...
3
votes
1answer
6k views

How can I duplicate a row in Google Sheets while retaining all of the formulas and format?

I need help with Google Sheets. We are using a sheet as a working file and new rows will be added by different users. Formatting and formulas are included in various columns. How do I ensure the new ...
3
votes
1answer
2k views

Reference Cells in Sorted Filter View

I have a Google Sheet that looks like this: That Google Sheet has some Google Script that looks like Google This: function myFunction() { var ss = SpreadsheetApp.getActiveSheet(); var rng = ss....
3
votes
1answer
125 views

Selecting a single column from all rows for chart range

I want to specify a certain column from all rows (omitting the first few) to be the range for my chart. Currently I input this to specify the range: L6:L (note: this automatically changes to L6:L20 ...
3
votes
1answer
1k views

Simultaneously sort multiple ranges on a single sheet in varying orders

I am trying to find a way using a script if possible (or formula) that will allow me to reduce the number of operations that I have perform to sort data in a particular google spreadsheet. Currently, ...
3
votes
2answers
1k views

How to Modify Matched Cells Across Multiple Sheets with Google Script

My question is a fairly complex one, but I'll do my best to clarify exactly what I'd like to do and this requires some background: I have a Google Spreadsheet used for user administration in my ...
2
votes
1answer
37 views

Drag to fill/autocomplete dates only increases year, not day

Please see screenshot below. When I dragged on the blue square in highlighted cell, I only get the result below. I have set correct region in sheet settings. I have set the correct date format in the ...
2
votes
1answer
37 views

Replace first value in a given range (column)

How would I look up the first value < 0 in this range and replace it with 0? If the value <0 does not exist, I'd like the column to remain intact. I've been struggling with this for a day now ...
2
votes
1answer
16 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)/...
2
votes
1answer
26 views

How to elevate a conditional format rule to be the number one rule instantly

I would like to make a newly created rule for conditional formatting the top rule so it has precedent over all others but be able to do so without having to drag it to the top as this takes for ever ...
2
votes
0answers
24 views

Sheets: Getting the slope of the 10 cells above current

I'd like a formula for Google Sheets, but I'm having trouble figuring it out. Say I'm on B74, I want the equivalent of =SLOPE(B36:B63,A36:A63). However, I want something that will work even when I add ...
2
votes
1answer
34 views

Query date time

I am writing a function to extract date, where my Col1 consists of date and time and my Col2 consists of date only. =query({Sheet4!A1:Q},”select Col1, Col2 where Col1 = date ‘”&TEXT(today(),”yyyy-...
2
votes
1answer
26 views

How do I input a current balance in a cell from a running balance that will be updated frequently?

I know I'm not accurately explaining that but here's a sample spreadsheet of what I mean:
2
votes
0answers
59 views

Is there a way to get multiple share links from Google Drive in order?

I have 100s of files on my Google Drive where the files are sorted by name. All I want is the share link of each file in the order they appear in Google Drive. I tried selecting all these files inside ...
2
votes
0answers
15 views

Customize point style in scatter chart [Google Sheets]

Is there a way to add new point shapes (import new symbols) in Google Sheets charts? Also, can I customize there color fills, or remove the infill color?
2
votes
1answer
36 views

Howto sync offline sheet from Sheets app

I did some offline work on a google sheet file on my mobile using Sheets app. I actually did not realize that I am offline, as I never set this option, at least not on purpose. After I connected to ...
2
votes
1answer
1k views

How do I pulling in historical daily trade volume data in Google Sheets with Google Finance?

If I try =GOOGLEFINANCE(A1,"volume",A2) where A1 is the stock code and A2 is the date I'm querying, it comes back printing 'date' and 'volume' on one line as headers and then the data on the ...
2
votes
2answers
301 views

Group by in query Google Sheet select different columns

Want to get the results described on the screenshot. How can I achieve it. QUERY: =QUERY(A2:F12,"SELECT A, MAX(B), MIN(B) group by A label A 'G V1', MAX(B) 'LAST V2', MIN(B) 'FIRST V2'") Want to ...
2
votes
1answer
48 views

How do I increase google sheets cell number by adding 20 in a formular

I would like to use "+" or "- " in referencing a cell eg =i(10+23) = i33 . So that I don't have to increment the cell reference manually . Any help would be great ?
2
votes
1answer
26 views

Sums with Same Value - Follow-up Question

This is a follow-up to these questions and information. Thank you Jacob and Kowser for the formulae you shared many years ago! The information you shared is almost exactly what I need to simplify a ...
2
votes
0answers
139 views

How to Group Set of Rows & Skip One Every Time

I'm currently working in a Google Sheet that has about 12,000 rows. Because of the layout, I want to group every 5 rows, then skip one, then group the next 5. Right now, I have as script that is ...
2
votes
0answers
129 views

Create a pivot table with a column for each assignee, containing rows of task descriptions

I have a two-column table with the first column being "task description" and second column being "assignee". This is something I'm unable to influence. Crude representation: I'd like to be able to ...
2
votes
1answer
32 views

How do I get the sum of the values of cells adjacent to (left of) a given key?

For example, given the following table, I'd like to return the sum of all cells directly to the left of all cells that contain the string 'joe' (6.0). | A | B | 1 | 2.0 | joe | 2 |...
2
votes
0answers
53 views

How to make a cell autopopulate the date dependent on another cell

I'm trying to make a recruitment sheet for an online community I help run. What I'm currently attempting to do is that when we record a person has joined the Discord to column B, it'll automatically ...
2
votes
1answer
136 views

How do I stop Google Sheets from opening new tabs?

In Google Sheets, I have two completely separate worksheets. In Worksheet A, there is a cell with a link to open Worksheet B, and vice versa. When I click on the link, the other worksheet opens up ...
2
votes
0answers
43 views

How to use COUNTIF and UNIQUE

I have a Google Sheet with two columns. One containing a list with order numbers - the order numbers can be listed several times/duplicates. The other column consists of the a free text with the name ...
2
votes
2answers
926 views

Import button greyed out in Google Sheets menu for unknown reason

I'm trying to import a .csv file into my Google Sheet like I've done myriad times. However, occasionally, the Import button in the menu is greyed out (see screenshot). Reloading the document doesn't ...
2
votes
0answers
408 views

Auto sorting by multiple columns in Google Sheets

I have a data-set which I would like to have sorted with first column 6 and then column 7. From this post: How can I make some data on a Google Sheets auto-sorting? I have gotten ideas about making a ...
2
votes
1answer
40 views

Filter function with multiple variables google sheets

I want to be able to use a filter function for people in NAME whose P DATE is within a specified range. However, if they have a Y under VALIDATED, I want the filter to also include names with the T ...
2
votes
2answers
2k views

Set calculation precision in Google Sheets

Google Sheets uses some sort of floating point arithmetic in its internal calculation, even if only integer numbers are in the calculation. For example: A1: =128^128 A2: =A1+1 A3: =A2-A1 Then, A3 ...
2
votes
0answers
67 views

Custom number formats in Google Sheets: Issue displaying negative numbers

Context: Looking to display large numbers used in financial reporting & analysis in an abbreviated easier-to-read format. I currently have the GS custom number format as follows: [>=999950]#,...
2
votes
0answers
331 views

*UPDATE* Hide/unhide certain columns on some conditions

UPDATE: I FIGURED IT OUT!!!! YEY!!!! Im trying to have certain columns in my Google Sheet hide or unhide, depending on some conditions. I realized that this is difficult. But I've managed (I think) ...
2
votes
1answer
859 views

Importing data into Google Form response sheet with date, list, grid

I have a normal Google Form that is filling answers into a response spreadsheet but I want to import pre-existing data stored on another Google Sheet (100 entries) that match the Google Form responses ...
2
votes
0answers
300 views

How can I create a violin plot in Google Spreadsheets?

How can I create a violin plot in Google Spreadsheets? Example of violin plot:
2
votes
1answer
99 views

How to generate standard competition ranks (not dense) for my Google Sheet?

What I have is a leaderboard sorted according from high to low scores. I would like to assign standard competition ranks (1224) formulaically. All 7 people who have the highest score (30) should be ...
2
votes
0answers
67 views

IMPORTRANGE, where range shifts often, to different spreadsheet

What I have: a client information spreadsheet that contains company information including their last order date following the status of their account (active/follow-up). What I need: the last order ...
2
votes
1answer
39 views

Add item to a google sheet's cell from url?

Is it possible to pass a URL to a google sheet and have the sheet append a new cell with content taken from a parameter in the URL?? Use case: In KDE (and other environment as well, perhaps), most ...
2
votes
1answer
55 views

Reference formulas from one sheet into multiple sheets

I would like to reference (pull in) formulas (not the result of the formula) from one sheet into multiple sheets so that they calculate on the sheet they are referenced. For example, if I have =...

1
2 3 4 5
32