Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. 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 chances to get a satisfactory answer.

1,168 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
2answers
6k 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
5k views

Set title of currently selected chart from named cell

There's a sample over here that will show how to set the first's chart title to the active cell: function onOpen(){ SpreadsheetApp.getUi().createMenu('Charts').addItem('Update title of the first ...
4
votes
2answers
789 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
317 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

Programmatically create an internal hyperlink

I need to programmatically create a large number of links in a Google Sheets that point to a specific range in another sheet of the same document. I tried to use named ranges for that but for the ...
4
votes
1answer
2k 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
0answers
39 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
77 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
0answers
90 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 ...
3
votes
3answers
68 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
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 ...
3
votes
1answer
119 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
743 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
992 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
891 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

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
3answers
38 views

Google Sheets: avoid decimal point when integer - [Bug discovered?]

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
33 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
31 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
19 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
18 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
41 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
30 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
1answer
26 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 ...
2
votes
1answer
10 views

Touchscreen tap edits drawing object, doesn't run assigned macro

In Google Sheets, I created a drawing object and assigned it a macro. When I use my laptop in "normal" mode using the touchpad (or at my desktop using my mouse) I can execute the macro by clicking on ...
2
votes
2answers
90 views

How can I quickly change the color of text in a cell in Google Sheets?

How can I quickly, with either a single or double click, assign a color to the text in a cell in Google Sheets? In order to quickly determine the status of certain pieces of data in a spreadsheet I ...
2
votes
1answer
83 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?
2
votes
1answer
294 views

Struggling to Generate Chart From Existing Data

This question is specific to Google Sheets—hopefully I’m in the right place. https://docs.google.com/spreadsheets/d/161rtpjNK5363-U6y6MZYzqOIx7CQBCHDWInBXFtZYIg/edit?usp=sharing As you’ll see above, ...
2
votes
2answers
2k views

How to change the values of a pie chart to absolute values instead of percentages in Google Sheets

I want to be able to change the percentages under each of the fruit names to absolute numbers (the respective quantities). I tried changing the 'slice label' but that only added the numbers over the ...
2
votes
1answer
30 views

Google Sheets date conditional formatting

I have dates in column D in Google Sheets. I want the cells to highlight red if 30 days have passed and there is not a comment date in column F. Example: Column D: Submittal Date 6/29/2019 ...
2
votes
1answer
11 views

How to create draggable sheet reference with the same cell?

I'm not very familiar with Google Sheets and have a question that may be very simple but I can't seem to find an answer. I am trying to pull information from one same cell example, H47 from multiple ...
2
votes
1answer
83 views

Dot leaders in Google sheets

Dot leaders are repeated dots that visually connect a label to the corresponding information on the same line, commonly used in tables of contents but also used in accounting formats, for example: ...
2
votes
1answer
557 views

Enable grouping and ungrouping of rows in a locked worksheet (Google Sheets)

I need to share a spreadsheet with some locked cells, but it is necessary that the user can use the grouping and ungrouping functions of rows. Within the pooled range there are locked and unlocked ...
2
votes
3answers
149 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,", ")...
2
votes
1answer
26 views

IF Cell = True, then retrieve (separate) Cell Value

Cells: T2 = Price to Pay U2 = (Tick box) V2 = =if(U2=True, "Paid", "To Pay") W2 = empty What I want to do is be able to tick U2 and have W2 contain the value of T2. V2 works, so it does not ...
2
votes
1answer
41 views

Timestamping purchase entries

I’ve created myself a budget tracker where I can track what I bought and how much I spent across a few categories. I specified column B (purchase entries are rows) to be the date and time at which I ...
2
votes
0answers
998 views

Change tab color based on cell value that is a formula OR cell background color

1st time question asker, so I'm apologizing in advance if my question is worded wrong or doesn't make sense or is, frankly, to wordy (I tend to over explain). I have a sheet with, currently, 10 tabs. ...
2
votes
1answer
21 views

Using multiple IF and OR statements with calculations

I'm trying to calculate prices for items that have various costs. Depending on what that cost is determined its multiplier. I'm hoping I can create a formula in a spreadsheet to streamline my process. ...
2
votes
2answers
965 views

How to plot dates on a timeline in Google Sheets

I have a column of dates, eg. 2017-01-20 2017-01-25 2017-01-27 2017-01-30 2017-02-03 ... 2018-12-20 I would like to create a plot where the x-axis is a contiguous range of dates from start to end (...
2
votes
1answer
2k 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 |...
2
votes
1answer
285 views

Add a new row to a spreadsheet, copying down formulas when a certain cell reaches a particular value

I've got a simple Google Sheet (it's literally one line), that has in A2 =now() to calculate the current time, and then a series of formulas calculating various dates/times based on the current date ...
2
votes
1answer
65 views

Is duplication of Structure and Behaviour a 'fact of life' in the world of Google Sheets. Is it possible to reduce that duplication further

Please note, I am not asking how to remove duplicates from a column of data. We are transitioning to a relational database system to store data. But, in the mean time we are using Google Sheets to ...
2
votes
1answer
1k views

Catch New Rows in Existing Filter Views

I have a series of filter views that filter my Google sheet based on the values in column B. I have 200 rows in my sheet, so the range for each filter view is A1:C200. Often, new rows get added to ...
2
votes
1answer
387 views

How do I reference the previous column of a calculated field on a pivot table? Looking to calculate percent change

I have a pivot table with a bunch of columns indicating the value over time, and would like to create a calculated field showing the change over each value. How would I reference the previous (and ...
2
votes
1answer
2k views

How to set Monday as start day of the week in Google Sheets date picker

How to set Monday as start day of the week in Google Sheets date picker? I have changed the locale, changed start day of the week in Google Calendar and can't find a way to solve this.
2
votes
1answer
329 views

Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet with a keyword instead of exact match

I would like to use the importrange formula to import data from "spreadsheetA" that contains "keyword" in columnD. I can get importrange to work but I cannot figure out how to filter it so it will ...
2
votes
1answer
145 views

Add values between two dates in Google Sheets

So I've already found this question answered several times around here but for some reason, despite essentially copy and pasting the answers it still doesn't work for me. I'm trying to add ...
2
votes
1answer
43 views

Split CSV cell, concatenate a value then recombine?

I have a spreadsheet where I want to take the CSV contents of a cell and prepend a value to each value. For example: BEFORE: A,B,C,D PREPEND: z AFTER: zA,zB,zC,zD I currently have this =JOIN(",",...
2
votes
1answer
39 views

Script to place duplicate Google sheet beside the sheet it copies (instead of the end)

Is there any way to modify my script to have the sheet it's copying insert the new sheet immediately to the right of the one it's copying? I have tons of tabs in the file, and it's starting to get ...