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
2answers
23 views

Google Sheets duration format does wrap around 24

If I format the cells of a sheet to duration, it wraps around 24h So adding those three cells (which should represent HH:MM): 05:00 18:00 02:00 with SUM, the result is 01:00 where I need the result ...
0
votes
2answers
30 views

How can I make sure an equation doesn't give an answer if more than one cell is blank?

I have half the equation working: =IF(AND(IF(NOT(ISBLANK(AG22)),TRUE,FALSE),AJ22<AG22),"1","0") So it looks like this: What I WANT it to do, is to only prompt an increase or decrease response if ...
0
votes
2answers
18 views

How do I “bin” results in Google Sheets?

I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale. For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or ...
0
votes
1answer
33 views

Changing a cell value based on another cell's color?

In my Google Sheets in column F, I want to include the word "LATER" based on magenta color (#ff00ff) that is in column B starting from row 227, how should I write this script? I'll really appreciate ...
0
votes
2answers
22 views

Show values from only rows that have a checkbox ticked

I have a data table with groups as column headers and names and emails as rows. I want to export data to another spreadsheet in the same document, which would only display members who belong to a ...
0
votes
2answers
31 views

Using TODAY function with QUERY

I am using this formula (see below) to try and retrieve dates automatically between the current date and the next. However, I keep getting the error, "QUERY completed with an empty output." =...
0
votes
1answer
13 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 ...
1
vote
2answers
21 views

Searching a list and replacing value if any item matches any item in the other list in Google Sheets

I have a list of usernames on two different sheets. I need to scan the first list of usernames to see if they appear in the second, and if they do, replace them with a predefined username. I can't ...
0
votes
1answer
24 views

How To Create a Globally Accessible Apps Script for Docs and Sheets?

I've been wanting to create a script that is accessible from any newly created Google Doc or Google Sheet. Is there any way to accomplish this? For example, if I wanted to create a script that ...
-1
votes
2answers
15 views

Value of Cell A1 could point to values of different cells

I am having difficulty with the following. Cell A1 could have values from 1 to 50. I have a listing on a separate table of different values that 1 to 50 represents. (I.E. 1 equals 1.68) I would like ...
0
votes
0answers
25 views

Send multiple emails with the help of Google Script while using different attachments

I have a Google Spreadsheet with a list of emails. I want to build a routine that send email automatically to those email addresses. I also want to attach different PDF/xlsx to this email as per the ...
1
vote
2answers
38 views

Remove Count column by repeating rows in Google Sheets

I have a worksheet, 'wi', in a Google Sheet similar to the following example but with an undetermined number of rows: Count | Entry | Description -------|-------|----------------- 2 | ...
0
votes
2answers
30 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
2answers
30 views

Showing top 5 most frequent values and counts of each from a data range

I have a Google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending ...
0
votes
2answers
21 views

Mark all line if checkbox is true

I will give an example: I have checkboxes in B3:G3. This is a checklist, I want to change the background color to green for A3:H3 if B3:G3 boxes are checked. Can someone please tell me how to do ...
1
vote
1answer
28 views

Visualize Data that updates dynamically

TLDR: How can I create a bar chart that updates the x-axis daily? The end goal is to have a chart that shows a side by side comparison of goal completions performance for each source of traffic. Here ...
0
votes
0answers
8 views

Google Sheet Pivot “% of Parent Row Total”

Can Google Sheets pivot table calculate "% of Parent Row Total"?
-1
votes
1answer
15 views

What do <> (angle brackets) mean in Google Sheet formulas?

What does <> mean in a spreadsheet formula, such as: =A1<>B1
2
votes
2answers
22 views

Multiple IF-statements in a single cell - checking long list of results [duplicate]

I have a Google Sheets where I enter all the results of my beer pong matches with friends. I'd like to see my amount of wins/losses with certain friends. This can be done with =IF(AND(B2="Won",E2="...
-1
votes
3answers
13 views

How do I sort data by size and not in ABC order

I am new to Google Sheets and have only used Excel a little, so Google Sheets is a bit different. I want to sort our band uniforms in size order from smallest to largest but Google Sheets only sorts ...
0
votes
0answers
14 views

Disable printing / downloading spreadsheet for editors - Google Drive Sheets? [duplicate]

I know it is possible to disable printing and downloading a Google Drive Spreadsheet for viewers, But is it possible to disable the same for editors other than the owner ?
1
vote
1answer
15 views

Create a Google Sheets graph with multiple lines in the same chart

I have data that looks like this: Name X Y Bob 0 15 Bob 3 20 Bill 0 24 Bill 5 30 And so on. Basically I want to have a line for Bob, a line for Bill, etc. With X as the X-...
1
vote
1answer
20 views

Allow others to make google spreadsheet copies that retain the view/edit permissions of its parent spreadsheet

I have a google spreadsheet with the permission "anyone with the link can view" so that I'm the only one able to edit but people can look at it freely. I have also been giving people the https://docs....
0
votes
2answers
16 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 ...
2
votes
2answers
25 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
2answers
29 views

How can I set this up to run two onedit functions in the same sheet?

all If you put the two together they don't work What is the right code to work together Note that the first to arrange automatic The second is to set the date if you put only one works, but if you ...
0
votes
2answers
46 views

IF and IFS not working properly

I am trying to locate data present in different sheets. I have tried using IF and IFS. Below are the formulas I have used. For IF, I get NOT IN USE even when I know there should be one of the sheets ...
0
votes
1answer
20 views

Trying to use Google Sheets importHTML() to import a table. But no content appears

I'm attempting to load the following website into Google Sheets: https://www.lasvegasadvisor.com/vegas-lines/?sport=ncaafb&period=0 Can anyone help provide a formula that would work in google ...
0
votes
0answers
18 views

How to customise ordering of segments in stacked waterfall chart using Google Sheets

I've drafted a stacked waterfall chart in Google Sheets (shown below). By default, the ordering of stack segments are as follows: In Column A: Apples, Bananas, Carrots, Durian from top to bottom In ...
0
votes
0answers
16 views

Unable to position custom data labels in center in Google Sheets waterfall chart

Context I've drafted a stacked waterfall chart in Google Sheets, with custom data labels (guided by Ben Collin's tutorial on custom data labels). My goal is to have the custom data label positioned ...
0
votes
0answers
16 views

Match based on row with lowest value

I'm finding the index of a row using another text value in a row as a reference to add a value to a "unit cost" column: =INDIRECT("Sources!I"&MATCH(A5, Sources!A:A, 0))*CONVERT(C5,D5,INDIRECT("...
0
votes
0answers
36 views

Using Google Forms to create an automatically updating inventory

I have created a Google Form that is used for people in the office to request items (swag given to donors). After an item or items are requested, the request must be fulfilled by a team member. I ...
0
votes
0answers
9 views

Google Sheets ImportHTML formula not pulling table I need [duplicate]

I have a Google Sheet trying to pull website table using ImportHTML formula to get a table from https://mesowest.utah.edu/cgi-bin/droman/raws_ca_monitor.cgi?state=AZ&rawsflag=3 ImportHTML(https:...
2
votes
1answer
18 views

Conditional Formating for Google Sheet comparing to list of text (highlight duplicates)

I am trying to create a custom formula for conditional formatting. I want to highlight the cell if the text appears in a list of names. This list will contain 210 unique names so I can't type each one ...
1
vote
1answer
21 views

How to countif with dates before today's date that auto updates

I am trying to use countif to count a certain number prior to today's date. So if today's date is 8/15/2019 the formula would count the number that is the B column that had a corresponding date prior ...
0
votes
1answer
18 views

Query runs but returns first row greater than the DATE at 00:00:00 not the row after 10:00:00

Trying to use the following query: =query(WABrdg729!A:B, "select A, B where A > date '"&text(datevalue($A$6), "yyyy-mm-dd")&"' AND A > timeofday '"&text(timevalue($A$6), "hh:mm:ss"...
0
votes
1answer
20 views

Conditional Formatting in Google Sheets based on the same rows conditional format

I have a spreadsheet where I have tasks, and I prioritize those tasks based on high, medium, and low levels. I currently use conditional formatting for this, so if I select from a drop-down "HIGH", ...
0
votes
1answer
24 views

Sequential numbers via formula - Google sheets - IF & MAX

I use this formula to find highest value in column A of other sheet, and then add 1 to it to get new unique number for a record ID, and I use... =if(B3<>"",max(Backend!A3:A)+1,) with this ...
0
votes
0answers
11 views

Automatically copy new imported entries into another sheet

So I'm using importjson to import a json feed into my google sheets. =importjson("url") It updates with 10 new entries a day. However, it doesn't save old entries, it just updates with new ones. ...
0
votes
0answers
20 views

Race results as race is being run

i have a sheet for a multi lap race. At any point I want to print the current standings. Racer D is currently leading having gone the furthest, racer B in 2nd, and so on. How can I get the current ...
0
votes
1answer
21 views

Google Sheets - summing numbers across a spreadsheet depending on the Column header

I am looking to automate our timesheet process. Each staff member has their own separate Google Sheet which has the dates down one column, and then a varying number of columns named after different ...
1
vote
1answer
18 views

Concatenation within IMPORTRANGE

I'm completely stuck on this one -- I need to use IMPORTRANGE function but want to make the sheet it pulls from flexible via concatenation -- here is my formula with a scrubbed URL: =importrange("...
0
votes
0answers
38 views

Open Gmail compose window in same window if clicked on Google Sheets comment reply?

Below is an example of an email received from Google Sheets comments. If I click on "Reply", the compose window opens in a new browser tab. Is there any way to make this compose window open in the ...
1
vote
0answers
13 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
44 views

Create table showing difference between values in two pivot tables

Our bookstore has two separate documents in Google Sheets (one containing purchase data and one with sales data). Each document contains a sheet with a pivot table that sums up total purchases/sales ...
0
votes
1answer
11 views

How can I group rows in Google Sheets and sort

I have been trying to group rows in Google Sheets. I have a heading (name) and then the information that must be included on the sheet without losing the spacing or lines. How can I do this ...
1
vote
1answer
26 views

Extracting specific phrases from a cell and echoing them in another

Longtime learner, first time poster here! I was wondering if anyone knew how to extract specific data points (phrases) from a Google Sheets cell and echo them in another cell. For instance, in my ...
0
votes
0answers
13 views

I need help auto-incrementing a column in google sheets based on data in another column

I'm working on a spreadsheet linked to a google form and I need to generate an ID for specific response. The form has 2 types of entries: New Entry and Update Entry. I need to assign an ID to the New ...
0
votes
0answers
10 views

How to make one cell automatically update a timestamp every time another referenced cell is updated? [duplicate]

I want to make a cell in my spreadsheet that updates a timestamp every time another cell is updated. I have a SUMIF connected to a budget inflows/outflows tab in my spreadsheet and I want this cell ...
1
vote
1answer
10 views

Setting a trigger to send mail when data is added to a row that was empty

I`ve got an external app that sometimes adds data to the last row of a sheet, is there a way to trigger this to send a mail with the content of that row?