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.

Filter by
Sorted by
Tagged with
0
votes
1answer
18 views

Google Sheets: Generate column in query based on reference range matches?

I'm no sheets expert, and I couldn't find the answer searching likely due to not using right search terms. Sorry if this is already answered and sorry if it's not possible. We have a data source that ...
0
votes
1answer
22 views

How to get the SUM total value to appear in cell vs the formula?

So I have created a spreadsheet to add cells =SUM(B2:B20) ... When I hit enter the sum value should be in the cell I created the formula but instead, I have just the formula after hitting enter. How ...
3
votes
4answers
6k views

How can I trace precedents in Google Sheet

In Excel we can trace precedents and dependents. Can some one help me with something similar for Google Sheets? I have got the code for dependents from https://webapps.stackexchange.com/a/50149/88163....
1
vote
1answer
180 views

Cumulative Percentage Subtraction Google Sheets

I have two columns in Google Sheets, where A = score and B = number of absences. After each absence, A will decrease cumulatively by 8%. For example, if A=100 and B=2, then the answer should be 79.28 ...
0
votes
1answer
21 views

Google Sheets Query - Group by more than one column without a pivot table

I have a timesheet dataset containing 4 columns: Column 1 - A workers name Column 2 - The date they worked Column 3 - The project they worked on Column 4 - The hours they worked on that date, on ...
0
votes
1answer
146 views

Filtering a QUERY using MATCH - how to pass column value into function? Or is there a better way?

I want to get a subset of data from another tab. Eventually, this will become a script and pull data from a different sheet. I can use QUERY to get all the data available, but I only want to return ...
0
votes
0answers
6 views

How do I get the selected cell on another sheet?

ok here is the manual situation in the spreadsheet: 2 sheets named Green and Blue Blue is the active sheet and B3 is selected. If I tab over to the Green sheet, D7 is selected. So I know somewhere ...
1
vote
1answer
4k views

Auto insert column with today's date every day

I would like Google Sheets to insert a new column between columns A and B at the start of each new day with that day's date in cell B1. Is there a function or script that would make this possible?
1
vote
2answers
57 views

How to count the number of empty rows since the last entry in a column?

I'm trying to get Google Sheets to tell me how many rows it has been since the last "1" was present in column A (see example table below). How do I get Google Sheets to output column B? An ...
1
vote
1answer
55 views

Google Apps Script: Is it possible to log the use of the close button on a Modal Dialogue?

I'm working on a spreadsheet which opens a modal dialogue when a script begins to execute. Inside it plays a google-style "working" animation made with CSS: If I could remove the close button so ...
0
votes
0answers
10 views

Finding a cell value based on row and column

I have a little problem that might not be solved just with formulas. And I am not so advanced in JavaScript yet. I have a list of products, our salesmen, customers and time when and how much the are ...
1
vote
1answer
68 views

How to concatenate several “filled” time slots in a spreadsheet and then check if they completely fill a “main” time slot?

For each row that represents a day of the month (month day 1, month day 2, month day 3 and so on...), I need this spreadsheet to answer NO at column C if no employee will be in the office for at least ...
0
votes
1answer
12 views

Continuation token: Generate file listing of a google folder into a google sheet

I have a folder on Google with 18k files and need to list some variables from them into Google sheet. A solution for this was found on Insert image from Google Drive into Google Sheets However, ...
2
votes
1answer
11 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
196 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 ...
0
votes
0answers
5 views

How can I search multiple terms for “text contains” using a custom formula with the Google Sheets filter tool?

I want to use google sheet's filter tool to search multiple words under the "text contains" option. However, it only let's you search one term at a time. I have tried using different formulas under ...
0
votes
2answers
26 views

How to filter after parsing CSV values contained in Google Sheets cells

I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
1
vote
2answers
23 views

How to find a spreadsheet to which a form submits?

In my organization, we have recently found a form that is supposed to collect some kind of submissions from people. The problem is that we don't know who exactly created it and where are the answers ...
0
votes
1answer
55 views

Average first 3 values excluding empty cells in google sheets

Trying to find the average of the first 3 values in the column. I'm already running a query to find the average data within the last 3 days =average(query({$A$10:$A$107,$E$10:$E$107},"Select avg(...
1
vote
1answer
82 views

How do I prevent or omit a negative value when calculating between times?

I've researched through the site to see if this has been answered but with no luck after reading: How do I calculate a time delta in my Google Spreadsheets? Calculating time difference Calculating ...
0
votes
2answers
497 views

Consolidate columns from a dynamic range into a single column in Google Sheets

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range ...
0
votes
1answer
79 views

When editing a formula in Google Sheets, how do I resume 'range selection mode' so that I can select a range with the mouse or keyboard?

When you first enter a formula in Sheets you can use the mouse or cursor to select the range. If I want to sum the 5 cells below the cell with the formula, I just type =SUM( and then use the down ...
0
votes
1answer
39 views

Pull unique values from other sheets as new sheets keep getting added

I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...
72
votes
17answers
98k views

In a Google Spreadsheet, how can I force a row to be a certain height?

In a Google Doc spreadsheet I would like to have each row the same height no matter how much text is in each cell. I have tried "resize row" but no matter how many pixels I put in, the row expands ...
0
votes
1answer
11 views

Google Sheets calendar script is timing out

function onEdit(e) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); if(sheet.getName() != "Sheet2") { return; } var lastColoum = sheet....
4
votes
7answers
13k views

Is there a way to set a default font in Google Sheets?

I know that there is a way to set a default font on Google Docs, but I was wondering if there's a way to set a default font in Google Sheets?
0
votes
1answer
782 views

How to set cell value based on 2 conditions of 2 other cells?

I've got a spreadsheet where people enter in values into columns A through H with I being a checkbox. On another sheet, it keeps a running total of all of the values of column E based on the values of ...
0
votes
1answer
9 views

What's a google sheet formula for fractional part of the number

I need a formula to get only the fractional part of a given number. It should take any number and return its fractional part which is between 0 and 0.9999... For example 123.456 -> 0.456 0 -...
1
vote
3answers
6k views

Google sheet limit, number of sheets per workbook

I know that Google sheets has a 2 million cell limit. https://support.google.com/drive/answer/37603?hl=en but was wondering is there a limit to the number of sheets? the old google sheet has this ...
-1
votes
1answer
16 views

No logger output in sheets

I am getting no output from the logger in sheets. I am a boob, although a 30+ year programmer. Here is my function: function onChange(e) { var spreadsheet = e.source; var cell = spreadsheet....
0
votes
1answer
27 views

Change a date reference as text to Google Sheets date format

Need to change "December 23, 2019 at 08:31PM" to a date Google Sheets recognize.
0
votes
2answers
51 views

How to Clear Cell(s) based on the Value of another Cell

I am looking for a script that will clear cell(s) when the value of another cell changes or is equal to ("X"). In the attachment I would like when Column "G" gets checked marked (TRUE), then ...
0
votes
1answer
18 views

Google Sheets: Fill out dates and replace blank cells with zeroes

I'm trying to make a timeline graph of seed sales, and right now it's almost completely unuseable: I think there are two reasons for this: Not all dates have values associated with them, and ...
0
votes
0answers
8 views

Accessing info inside links in google sheets

Background: I have a Google Sheet that has some links in it. Of course, links don't always have the contents of what you are looking for the URL, so trying to search for it later is hard. Question: ...
0
votes
1answer
20 views

Adding Positive Values Only (across multiple sheets)

The example formula below works perfectly in my spreadsheet if ALL the numbers are positive. However, I would like to pull the values from the cells indicated below (from 14 different pages), but ...
1
vote
1answer
220 views

How do you “Paint Format” more than once in Google Sheets?

In Google Sheets you can select a formatted row, click the "Paint Format" Tool, then click another row to apply the same format. You can drag down to do multiple rows if they are together (say rows 50-...
1
vote
1answer
25 views

How to create comma and hyphen separated list of sequential numbers in Sheets?

How do I create a list of numbers, where consequential numbers are reduced to being separated by a hyphen? e.g. I have a column with numbers like 1 2 3 5 6 7 8 10 12 I want this to be reduced to 1-3,...
0
votes
0answers
18 views

Is it possible to acheive this in google sheets?

I want to implement the following in google sheets, but I don't know how to or if it is even possible I have a sheet, let's call it "products", in which every entry has an ID tag and a value I want ...
0
votes
1answer
42 views

If cell y contains any word from range x:x, mark cell y green

In Google Sheets, I have three columns (with different lengths!) Column1 contains firm names (50) Column2 containts firm names (100) I would like to mark a cell in Column1 green (or with a number in ...
0
votes
0answers
14 views

Google sheets split script?

I am trying to figure out a script that basically splits a cell horizontally in a google sheet that auto populates with a form. =SPLIT(AF5, ",") formula works, but I when a new form entry is added, ...
0
votes
1answer
20 views

Can I retrieve the names of sheets in a public document?

I am currently using the function IMPORTRANGE in a Google spreadsheet to pull information from a public document using the URL. I have to supply a range_string in the format "Sheet Name!A1:D21" to ...
1
vote
1answer
75 views

Google Sheets Conditional Formatting only applying formatting to certain cells

I am currently working on making a spreadsheet that will figure out which class I should be in based on times. That all works and outputs if I should be in the class to a TRUE/FALSE cell. My ...
0
votes
2answers
40 views

Suming values in a column if value in cell next to it matches another cell

I'm trying to create a sheet to maintain a small inventory of servers. I created a segment where each type of component will be listed at the top. Below it is a series of copy-pasted chunks which ...
0
votes
2answers
39 views

Extract the Day of the month (dd) from a date in Query()

I have a list of transactions for a month, all include a date (Column B - dd/mm/yyyy) and a £in figure (Column F). I want to create a table that shows the total taken on each day of the month so for ...
1
vote
1answer
95 views

Google Sheets row minimized to almost nothing when no data is present but needs to auto expand to be fully seen ONLY when data is present

I've got a Google Sheet I'm creating for settlement purposes and I have 8 variables that only get used sometimes. Each variable has its own line devoted to it. For example, this load has a scale ...
0
votes
0answers
11 views

Why isn't there an undline button in Google Sheets?

In word processing and spreadsheet applications, there's always the "big three" formatting options that have quick-access buttons: bold, italics, and underline. Why is underline missing in Google ...
0
votes
1answer
23 views

Google Sheets' Column Lock

Does anyone know how to lock a column in Google Sheets without affecting other columns if there are any structural changes in the sheet. For example, I'm trying to lock one column, but when I try to ...
0
votes
0answers
13 views

Insert range using IF formula in SUMIF function with multiple criteria (using arrayformula)

I used an arrayformula with my sumif function which has several criteria and ranges to expand automatically across a set column (B). The formula below works perfectly: =ARRAYFORMULA(SUMIF('DATA'!$N:$...
1
vote
1answer
111 views

Chart labels disappear (stays outside the graph area) Google sheets

Really count on your help here, as it seems like I tried everything already. Take a look at the graph: the last value label disappears (the location is right on the inner border of the graph, so the ...
1
vote
1answer
36 views

Conditionally format a column if it contains all the values in a set

Each of the columns in my Google Sheet represent a farm. Each farm is a list of animals. I want to highlight the entire column if the farm has a duck, a horse, and a cow. So the column may look like ...