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,413 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
9
votes
1answer
4k 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
3answers
12k views

How to reverse the y-axis in a Google Sheets graph?

I have data that represents the depth of an open mine in a transversal view. Currently, it looks like this: But since it represents the depth of a mine, I'd rather have it "flipped". So the y-axis, ...
4
votes
1answer
2k 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?
4
votes
0answers
354 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
2answers
1k views

CountIf in upper cells with ArrayFormula

I have some values in the column A and in the column B want to calculate concatenation of the value and how many times it was used in rows above. In the usual formula, I would've written in B2 =A2 &...
4
votes
1answer
372 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 ...
4
votes
1answer
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
3k 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
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
288 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
95 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
123 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
2k 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
0answers
4k views

Showing percentages in google sheet bar chart

It looks like bar charts in Google Sheets won't support percentages. Is this correct? If so, how would a change the populating query that currently returns "count of"? A single cell used as the ...
3
votes
1answer
315 views

Use non-linear color scale for conditional formatting in Google Sheets

In Google Sheets, when you use conditional formatting on a column with numeric values, is there a way to place a colored scale that is non-linear, but exponential, for example? I know you could ...
3
votes
1answer
1k 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
164 views

How to automatically create a templated Google sheet?

I'm trying to figure out how to automatically create a templated Google Sheets worksheet when a new entry is made in a different sheet. So the flow would be: Person's name is added into Google sheet ...
3
votes
1answer
123 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
942 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
1answer
1k views

How to use Google Scripts w/ spreadsheet and jotform to auto sort so the newest entry is always listed on the top row?

I'm trying to setup a form and spreadsheet database to track inventory on a daily basis at a remote location. The idea is that the person at the remote location can update our stock via a simple ...
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
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
22 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
14 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
28 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
88 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
29 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
22 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
95 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
106 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
22 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
2answers
48 views

Extracting range from of an array

From the following single cell array: 0, 1, 1, 2, 3, 5, 8, 13, 21, 34 I'd like to extract 3,5,8,13,21 into a single cell by specifying index 4 : index 8 of the split of that cell. The Challenge is ...
2
votes
0answers
39 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
45 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
36 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
0answers
67 views

Google Sheets: insert a non http protocol link?

I use the Chrome extension Session Buddy to store browsing sessions. In this case, I saved a few windows with tabs on a search I just did but will read later. So, on a Sheet where I keep notes to ...
2
votes
0answers
127 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
32 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
697 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
37 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
4answers
958 views

Avoid decimal point when integer

Is there a way to use a custom number format in Google Sheets so that decimal point will be visible only when decimals are present? For instance 9 8,2 10 Is there a newer solution about achieving ...
2
votes
0answers
109 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
554 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
1answer
60 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
46 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
23 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
51 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 =...
2
votes
1answer
128 views

Google Sheets - Query Multiple Columns for same Data

We have Employee training data in a Google 'Form Response' spreadsheet which ranges from A1 to JQ1000. This spreadsheet is automatically populated when a Google Form is completed but it is difficult ...
2
votes
1answer
38 views

Automatically copy first 3 columns from one sheet to each new sheet in Google Sheets

I'm having columns A, B and C filled with data in Sheet1. Those columns contains data that is static for each new sheet. Currently, I need to manually copy the first three columns to each new sheet I ...
2
votes
2answers
55 views

Google Sheets formula for unique values across columns

I'm familiar with the UNIQUE function in Google Sheets and how it works on rows (see Examples 1 & 2 below). Is there a function in Google Sheets that operates similarly on columns? In Example 3 ...

1
2 3 4 5
29