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.

1
vote
1answer
19 views

Left join 2 sheets on 2 key values, add constant value

I'd like to join by date on these 2 sheets (Income & Expense), on key values month and day and add constant value from FixedCost sheet, Income: Expense: Add FixedCost to every row/date: ...
1
vote
1answer
34 views

Why can I only send 100 emails from Google Sheets?

Please, can somebody explain why I can only send 100 emails a day even though I am a G-suite upgraded user that's already passed the free trial account? Even if I was still on a free trial account, ...
0
votes
0answers
12 views

Why is not possible to activate an event only at single cell change in google sheet?

Why is not possible to activate an event only at single cell change in google sheet? It would be very usefull. Are there a way or the only possibility is to check the cells you want to control on a ...
1
vote
1answer
57 views

Google Sheet Query smarter way to get multiple criteria

I use a spreadsheet where I want a list of items which contains multiple criteria. (I need all cells in a range which contains the letter 'a - g' The formula I use now is =query(Planning!G5:G37; "...
0
votes
1answer
21 views

How to recall the previous info submitted and change some fields only for the next submit

In my google form to register for an event, users may need to register a few times for the different members of their group (e.g. family members) but some of the information is the same e.g. address, ...
1
vote
1answer
48 views

Auto update of cells with timestamp

I'm new to writing/using scripts. I want to use something to allow for multiple cells to be updated and tracked for the last time they were modified. I've tried various different scripts on here but ...
1
vote
2answers
20 views

Counta unique string on filtered list

I'd like to count unique string values on a filtered list (For example, the value should be 3 in this screenshot): How can this be done in google sheets? Here's the spreadsheet: https://docs.google....
1
vote
1answer
16 views

Entering new date in a column does not autoformat

I've already done Format > Number > More Formats > More Date and Time formats for the entire column but the new date entries are still not auto formatted, how can I make it auto format? Here'...
1
vote
0answers
10 views

How to change the default font and size in Google Sheets? [duplicate]

I want to set the default font size to 12 in google sheets. How?
0
votes
0answers
19 views

Google Sheets CSV Download Loses Data from Other Worksheets

I'm trying to save a single sheet in Google Sheets to a CSV. In my example it's historical stock quotes formatted for Quicken import. All of the "work" is done in other sheets - specifying date ...
1
vote
1answer
20 views

Link to a date cell in QUERY criteria

I have the following formula and it works fine =unique(query(ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY( {Database!A1:AB, (Database!N1:N)-(Database!M1:M)}, "where Col9 <> '' AND Col1 >= date'2019-...
-1
votes
0answers
27 views

Split total volume of each customer through the weeks, respecting weekly volume

I need to create a tool that splits the total volume of each customer through the weeks, respecting each customer's weekly volume, like below's picture: Columns A to F are the input, "Division by ...
2
votes
1answer
42 views

Apps Script to search for names and file paths in folders by name

I'm making apps script to find files that don't contain the names "Done" and "InProgress". The script that I have created is like this: function testSearch(){ var sheet = SpreadsheetApp....
2
votes
1answer
22 views

Google Exchange Rates build

I am trying to make a currency exchange rate Google sheet. I want a cell to give me a value of conversion based on the value of another cell. Currency type can be selected from the Dropdown List and ...
0
votes
1answer
15 views

Timestamp format with carriage return

I have this time stamp format: var timestamp_format = "yyyy-MM-dd HH:mm:ss"; Which produced a result like: 5/25/2019 10:52:43 but I would like to have a carriage return between the date and time ...
0
votes
2answers
14 views

Importing data found to be true from one Google sheet to another

I am trying to find a way to import data (text) from one sheet to another, via the same book. I assume it's going to need an IF or IFS formula because I only want data is that found to be "TRUE" (via ...
0
votes
0answers
11 views

Google sheets does not label one of my ponrts on my scatter plot

I don't know what to do. I have my data in a graph and all points are labeled apart from one. How can I fix that?
1
vote
1answer
26 views

Averaging cells that have both blanks and numbers for values that are in non continous columns with nine columns in between them

I need help averaging cells that contain numbers ranging from -1200 to 1200 approximately, or blank values only. I will be averaging them by groupings of 100 increments ie. -1200 to -1100, -1100 to-...
0
votes
1answer
12 views

How to find a string then find the sum of all cells next to the found string

So I'm trying to get G4 to automatically find "Bananas" in all of C and for every time it finds a value it adds up the corresponding number in D.
1
vote
1answer
29 views

Time not formatting as expected in Google Sheets

I imported a report to Google Sheets and the duration column shows 7 h 37 m or 14 h 5 m a similar format. When I replace h with : it still does not properly format to time duration and so I cannot ...
0
votes
1answer
65 views

Pivot Table Filters - Custom Formula

In my pivot table, I want to only display the entries in the column "Which Event" if the name contains "Baseball" or "Softball". I can very easily do part of this by adding a filter for the field "...
-1
votes
0answers
26 views

Google Apps Script GetUI, disable buttons

I'd like to show a popup while the script is running with no buttons so the user cannot dismiss it until the script finish running. Tried with GetUI and could not disable the buttons. Any ...
0
votes
2answers
30 views

Export PDF link sheet ID auto change

I have the link for export as PDF a specific range on a sheet =HYPERLINK("*****export?exportFormat=pdf&format=pdf&gid=962914045&range=A1:H68&pagenumbers=false&gridlines=false&...
0
votes
1answer
19 views

How can I track down the bottlenecks in Google Sheets? [closed]

Given a complex multitab Google spreadsheet, what tools and methodologies are there to find what is slowing down my sheet execution? I'm seeing behaviour where the ribbon that shows recalc progress ...
0
votes
0answers
15 views

Google sheet printing problem

I have my language set to English but whenever I am about to print, it changes to another language. I have tried this several times, but it's not changing; it keeps printing in the other language even ...
0
votes
2answers
25 views

IMPORTRANGE connect sheets

When using IMPORTRANGE on Google Sheets, one has to "connect sheets" as follows: Where can I check which sheets are connected, and what exactly (what access rights etc.) this "connection" implies?
1
vote
1answer
25 views

SUMPRODUCT inside ArrayFormula

I have a fixed vector, say $A$1:$E$1 I also have a vector per row, say A2:E2, A3:E3, A4:E4... Now I want output in Column F as F2 = sumproduct(A1:E1,A2:E2) F3 = sumproduct(A1:E1,A3:E3) F4 = ...
0
votes
2answers
22 views

Google Sheet use filters to sort with Unique

In google sheet, I have a data set A: In another sheet I am creating a dynamic list using the UNIQUE formula: However, when I use filters to sort the data, it gets all scattered leaving empty cells ...
0
votes
1answer
17 views

Conditional formatting formulas in Google Sheets

I want to use Conditional formatting so all of the cells in column A change colors whenever the matching cells in column B have the word "later". ex: If cell B20 contains "later", then I want A20 to ...
1
vote
2answers
17 views

Conditional Highlight: color change cell only if multiple others filled in

I have a shift sign-up sheet in which I would like the cells in column A to be highlighted only once there is a value (name) in the corresponding cells in columns B:D. In this example, I would like ...
1
vote
1answer
16 views

Conditional formatting cells with multiple conditions and relative references over a range

I have a Google spreadsheet with arbitrary data. Three adjacent columns (let's say A, B and C) have been set up with Data Validation to only allow values from 1 to 10. I'm looking for a way to ...
0
votes
1answer
24 views

QUERY with Week Number

I use query to extract two columns from another sheet with data. Column A has dates Column B - numbers I want to extract only rows that correspond to a specific calendar week number. For example 19. ...
1
vote
1answer
37 views

How to get and use a value of a cell in the PDF file name exporting using that script

How can I get and use a value of a cell to the PDF file name, when exporting/saving using that script. I would like to use a string written in a cell, I mean, put that information in a variable and ...
1
vote
1answer
18 views

Convert a text of a specific cell or exclude one cell from all text convertion to UPPERCASE

How to adapt this function to exclude a specific cell or how to apply just to one cell? function onEdit(e) { if (typeof e.value != 'object') { e.range.setValue(e.value.toUpperCase()); } }
1
vote
1answer
16 views

How to count cell with upper leter in array?

I need to count the upper letter if exist in a particular cell. So my results should be as on the image below. important is ignore in count empty cell. I noticed that task is simple if I can use ...
0
votes
1answer
17 views

Google Sheets Pivot Table, Summary by Day

I have a sheet with 2500 dates & times of when customers have placed their order. I want to do a heatmap or a bar graph to show which days of the week are the busiest. I rounded the exact times ...
0
votes
1answer
31 views

How do you get current date to be added to a cell when a check box is checked in another cell

I want to get the current date added into a cell when a tick box is checked in another cell. I currently have another formula in the date cell which changes its colour when the tick box is checked, ...
0
votes
1answer
12 views

Changing cells formatting based on another cells date

I've been looking and can't quite find the answer I'm looking for. I have a spreadsheet with columns Monday through Saturday with data lining up underneath. I would like the current day to highlight ...
0
votes
1answer
13 views

How do I turn a SQL database downloaded table into a Google Sheets multi-line chart?

I want to turn this table (comes from an SQL database) ...into a line chart in Google Sheets that looks like this
0
votes
0answers
13 views

Google sheets sub-chart in chart… possible?

I have 3 different data ranges that I need to plot on a google sheets chart. On the main chart I have different value ranges, thus I'll use a left y-axis and a right y-axis, but the 3rd data range is ...
0
votes
1answer
12 views

1 plot with several lines with a common X axis but unique X and Y values… is this possible?

I am trying to create a graph which shows several smooth lines that are defined by an X and Y value. the problem is that they have to share the X-axis. Is there a way to efficiently add the other ...
1
vote
1answer
36 views

Query select all values of specific month

A have a table where Column A has dates. I want to get all values of a specific month I have the following query for May for example =query(Database!A1:AD,"Select A,E,F,U,V,W,X,AB,AC where Month(A) =...
-1
votes
1answer
18 views

Ampersand causing issues in Google Sheets Formula

I was trying to use the hyperlink formula to create an automatic e-mail creation, however, when i try to pull an information that has Ampersand in it the formula doesn't end. Note that the formula ...
-1
votes
2answers
37 views

Change a cell to a colour when a tick box is checked in another cell

I have a cell with a checkbox. I want another cell which contains a date to go green when the tick box is checked. I presume this is done in conditional formatting but I am struggling with it.
0
votes
1answer
40 views

Formula not working on Google Sheets #error! Formula Parse Error

I have uploaded an excel spreadsheet that works fine but shows as an error on Google Sheets. =SUM(H12,N12,T12,Z12,AF12,AL12)/INDEX(FREQUENCY((H12,N12,T12,Z12,AF12,AL12),0,2)) Its calculating average ...
1
vote
1answer
20 views

Сopy cells depending on values in another cell

In Google Sheets I try to copy cells B2:B4 depending on values in other cells C2:C4. For example - example.xlsx made in Excel. But stuck and can't find a solution on how to do it in Google Sheets. ...
1
vote
1answer
26 views

Google Sheets Formula Import Range with Indirect

I have this formula and want the reference to Archery to reference Archery which will be in Cell E1. I tried using the indirect formula but can't get it to work with the formatting for Google Sheets. ...
1
vote
2answers
107 views

How to return values in a formula, from rows of latest 4 nonconsecutive dates recorded

Using an ARRAY_CONSTRAIN formula we want to capture the latest 9 records (rows) over a period of nonconsecutive dates. =ARRAY_CONSTRAIN(sort(vlookup(query({row(EBPP!D:D),sort(EBPP!A:E)},Q15,0), {...
0
votes
1answer
11 views

How to change cell range without deleting it?

In a function, let's say the SUM function, I have a range H25:H37 I want to change this range to H23:H40 using the mouse. The only way I could find is to delete the initial value, then I can drag the ...
0
votes
0answers
17 views

Custom color for every user in Google Sheets

We are using Google Sheets as a to-do list and would love to know if we can connect color to a person (editor) that adds something. We are working with 4 people in the same document and it would be ...