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.
0
votes
0answers
5 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....
1
vote
1answer
13 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
9 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
10 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
7 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
16 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
10 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.
0
votes
1answer
26 views
Google Sheets advanced alternate custom sorting
For example, I have a random list of students in different rooms 101, different gender, and 4 different categories. The list might grow and students might be added in new rooms or existing rooms.
How ...
1
vote
1answer
21 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
18 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 "...
0
votes
0answers
10 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
14 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
11 views
How can I track down the bottlenecks in Google Sheets?
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
6 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
18 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
11 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
1answer
15 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
11 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
11 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
12 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
10 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
27 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
15 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
13 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
14 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
24 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
8 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
11 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
10 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
8 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
14 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) =...
0
votes
1answer
12 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 ...
0
votes
2answers
21 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
30 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
17 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
14 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.
...
2
votes
2answers
62 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
6 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
9 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 ...
1
vote
2answers
36 views
Is there a way to highlight specific words in search results?
Is there a way to highlight particular words in cells that I enter into the search window? Sometimes, working with a huge text we need to find only several words among long text and we have to read ...
1
vote
1answer
10 views
Query with dropdown not working
In my Google sheet I'm getting an error:
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN:
I've added a dropdown so the formula will auto-update depending on the tab ...
0
votes
0answers
10 views
How do I get responses in Google Forms to automatically be updated when updating the spreadsheet?
I'm in a situation, where I have been given a source of information, with all sorts of people. And with that, I wish to insert all this inside of Google Forms, in the response section. And so I was ...
1
vote
1answer
21 views
Copy Sheet To New Spreadsheet
I want to copy one sheet in the Spreadsheet file into a new Spreadsheet file.
The sheet that I want to copy contains formulas, I want to just copy the value to a new Spreadsheet file.
I have made the ...
0
votes
1answer
10 views
Google Sheets populate X cells with formulas based on single cell containing value X?
Google sheets dynamically populate cells based on single cell value. I don't know what the term would be called and a bunch of searching hasn't resulted in any lead on what it would be called.
What I ...
0
votes
1answer
7 views
How do I make one filter take into consideration the order of other columns?
I'm using the Filter (the one you create with the button in 'menus') to sort my table, but when I click the filter icon in a column's header and select "Sort A→Z", it only applies to that column.
...
0
votes
1answer
12 views
Funny alignment in Google spreadsheet
This is driving me crazy.
I have searched the web for an answer, without success.
All results are simple pointers on how to use the formatting on the sheets.
This misalignment seems to be something ...
0
votes
1answer
17 views
Create 'bookmarks' in google sheets [closed]
I'm pushing the edge of google sheets, doing with a spreadsheet what ought to be done in a database.
As an example: Cust_Orders is a sheet broken into blocks of 15 rows. Each block is one order ...
0
votes
2answers
13 views
How to use IMPORTRANGE when I have multiple tabs?
I have to export some data from a Sheet with 30 tabs(a tab for each day of the months) and I have to extract data under specific criteria from all the sheets which have to be done every month on a new ...
1
vote
1answer
7 views
Set certain days of the week on repeat [closed]
I'm making an attendance sheet for my team, how do I make only certain days like Mon Tue Wed appear in the top column?
0
votes
1answer
9 views
Query Function Not Putting Consolidated Data in the Right Place
So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
