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,750 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
15
votes
3answers
8k 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
2answers
434 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 ...
5
votes
5answers
14k 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". In other words, I want to highlight them so that they can be copied, formatted, etc. How ...
4
votes
1answer
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
2k 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
2answers
547 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
135 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
572 views

How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters. I have this formula that only works for any types of duplicate words: =join(" ",unique(transpose(split(A1,", ")...
3
votes
3answers
337 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
8k 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
128 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
13k views

Keep multiple sheets synchronised (both ways)

I need to have multiple Google Sheets be synced both ways. For example: I have a "master" sheet that has all of my information. And then I have multiple "filtered" sheets. Each filtered sheet only ...
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
0answers
21 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....
2
votes
1answer
87 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
45 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 ...
2
votes
1answer
43 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
19 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
30 views

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
39 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
30 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
1answer
220 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
46 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
2answers
536 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
0answers
270 views

Why do Google Sheets filter ranges sometimes auto-expand to include new data, and sometimes don't?

When I create a filter in Google sheets, I want the range to automatically expand as I add/populate new rows. Sometimes, this works fine: When I have a column using “Checkbox” data validation (...
2
votes
2answers
115 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
35 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
177 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
137 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
60 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
1answer
69 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
235 views

How do I stop Google Sheets from opening new tabs?

In Google Sheets, I have two completely separate spreadsheet files. In Spreadsheet A, there is a cell with a link to open Spreadsheet B, and vice versa. When I click on the link, the other ...
2
votes
0answers
45 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
1k 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
735 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
47 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
0answers
896 views

How to check if there is any Data Validation error in the whole spreadsheet?

Is there a way to check in a Google Sheets spreadsheet, if there is at least one cell violating a Data Validation rule? If such functionality is not built-into the app, how can I write a Google Apps ...
2
votes
0answers
86 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
490 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
1k 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
465 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
117 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
73 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
61 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 ...

1
2 3 4 5
35