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.
5,077
questions
0
votes
0answers
4 views
Viewing & editing data in Google Sheets using a form
Question: Is there any way to view and edit data in Google Sheets in a form, like MS Access or OpenOffice Base can do that? It would be perfect if we could cycle through each record, which would be ...
0
votes
0answers
7 views
How to Sort different sheets by conditions?
I have 6 different sheets in my Spreadsheet
I want to sort the first 3 & last 2 (5 & 6) based on an ascending order dependent on value in Column H
However the 4th sheet I want to sort based ...
1
vote
1answer
11 views
AND / OR Conditional Formatting for Google Sheets with multiple criteria
I am trying to write a custom formula to conditional format column E by referring to the different tenure level targets in column D to indicate if they are hitting their targets base off of their ...
-2
votes
1answer
21 views
Google Sheets missing core range reference operators intersection+union/concatenation [on hold]
But Google Sheets' top competitors long have these core range reference operators:
Microsoft Excel long has range operators intersection+union per official http://office.microsoft.com/en-us/excel-...
0
votes
1answer
17 views
How to reference a dynamic range in Google Sheets Script?
I have a sheet where the filter originally started on row 2, so my range was A2:J. As I've added and deleted rows/columns, my range has changed and it's still changing as I edit the sheet.
Is there ...
1
vote
1answer
17 views
Query sum for unique pairs of rows
I have a spreadsheet which logs the duration an employee spends in a city. An employee can visit a city multiple times like Peter in this example.
City Person Days
London Tom ...
0
votes
1answer
10 views
Cut and paste data from a row of cells from one tab to another tab in Google Sheets and repeat with new data
The below code works for Q3 but not for Q5. I'm relatively new to sheets and writing code and I'm sure that you can't have two functions with the same name but am unsure how to resolve so that I can ...
1
vote
0answers
11 views
Update other sheet's rows based on pasted data
I have a worksheet with several sheets in it for social media analytics purposes. I would like to be able to paste data from a CSV I export daily into one sheet and have the data parsed and copied to ...
1
vote
0answers
11 views
How can I send an email, with an attachment from the user's local storage, using Google Sheets Script?
I'm new to Google Script but quite experienced in Excel VBA. I'm trying to set up a Google Sheet to enable sending of a given document by email attachment to a number of recipients.
I've got the email ...
0
votes
0answers
13 views
Copy the data from one sheet to another, assigned to a particular line
I have two google sheets. I need to copy the data from one sheet to another, assigned to a particular line. For the sake of ease of understanding, I changed the colors in the sheets example as they ...
-1
votes
0answers
7 views
Google Sheets pull data from second sheet where Row and Column are strings
I am trying to pull data from a different sheet in google sheets by matching row and column header
EG
SheetB
LOC prox dist qty
sjc 12 15 19
dfw 10 16 8
atl
ord
In ...
0
votes
1answer
11 views
Countifs in between ranges
I'm struggling to pull below conditions together. I'd like to have a formula that will count values between 1 and 5 only when cell U13 contains the text 1-5.
=COUNTIFS(U13,"1-5")
=COUNTIFS(K2:K22,"&...
0
votes
1answer
15 views
I want to get values from multiple URL's in Google Spreadsheet. IMPORTDATA doesnt work
I have 9000 lines with URL's in my Google Sheet.
Example: https://steamcommunity.com/auction/ajaxgetgoovalueforitemtype/?appid=233860&item_type=10).
Every URL returns JSON like this:
{
"success": ...
0
votes
1answer
21 views
How to partial extract from 3 columns with multiple levels of rules?
The formula someone on this forum provided worked well, but I have run into a problem, which I am struggling to get around.
Google sheets Link
The formula in H2 looks between 00 & 02 and ...
0
votes
1answer
17 views
Google sheets spreadsheet will not format date correctly
I have a Google spreadsheet with a date column formatted to dd mmm yyyy. Many of the dates are formatted this way, but some cells will not format this way, and no matter what I select under "Format", "...
0
votes
0answers
6 views
hide rows in Google Sheets based on cell value in a different row
I'm trying to build a worksheet in google sheets with a checkbox which will hide and unhide lines 5 thru 60.
My checkbox is in cell A4, and when checked cell A4 says "hide", and unchecked cell A4 ...
1
vote
1answer
31 views
ArrayFormula to extract part of the string
I have come across the below formula, and I am finding it quite useful, I want to turn it into an array shortly, but before I do, I was wondering if there was an easier way to do this?
=if(len(LEFT(...
1
vote
1answer
16 views
When I insert a row, indirect does not update
I have a summary sheet and I would like that every time I add a new sheet everything gets referenced to the summary sheet. I have used indirect to do this, the only flaw is that once I add a row, the ...
0
votes
1answer
6 views
Simplify fractional result of custom number format
In Google Sheets, I created a custom number format like this # ??/32 to express decimal numbers to the nearest 32nd fraction. So if I type 0.537 I get 17/32, but if I type 0.5 how do I get it to ...
0
votes
1answer
14 views
Unsimplified Fractions in Google Sheets
How can I get Google Sheets to display fractions in the form I enter them, rather than in a simplified form? I'm trying to evaluate how a student did on a test, so I have one cell that reports the ...
0
votes
2answers
10 views
How to archive cell changes in new tab?
I have a Google sheet that I'm collecting some statistics in. There is a particular cell that I'd like to track changes in a separate tab. I'm looking for a function to copy cell if the cell data ...
0
votes
0answers
13 views
Complicated Google Sheets Project [on hold]
I am a teacher and am designing a tool to help teachers design units and plan lessons. I am new to coding and this is becoming a bit ambitious for me. I am happy with what I have so far but there are ...
0
votes
2answers
15 views
Help with a Vlookup function to return multiple results of not exact match
I am trying to run the following code to return all the matches of column A from column C in a set of rows. In this situation, cell A1, is contained in a few iterations of column C, however, these are ...
1
vote
2answers
29 views
Combining IMPORTRANGE, SUMIFS, QUERY with cells that contain numbers and text
I'm having an issue combining SUMIFS with ImportRange in Google Sheets. I found answers suggesting to nest it with QUERY. However, the issue is that one of my criteria is non-numerical.
Formula in ...
0
votes
0answers
34 views
How Can I Ignore Null Cells in a Google Sheets Chart?
I'm generating a chart where I have to have some empty cells due to the way my data is formatted. However, I cannot figure out how to ignore those empty cells when generating a line chart. I've tried ...
0
votes
0answers
8 views
conditional formatting skipping colors after the same large value
Looks like the conditional formatting skipping large formula because of the same large values.
Help me please how can I get the order what is on the picture in the column "What I would like".
0
votes
1answer
15 views
Google sheets IF OR script
So this is the code that I'm using for the script that I found;
function onOpen() {
var s = SpreadsheetApp.getActive().getSheetByName('Todo');
s.showRows(1, s.getMaxRows());
s.getRange('D:D')
...
0
votes
1answer
9 views
Process visualization based on data
I am trying to use a Google spreadsheet to visualize a customer journey.
I am trying to create the following graphic:
I have created the following example spreadsheet:
https://docs.google.com/...
1
vote
1answer
30 views
Simplify multiple OR statements in a QUERY using IMPORTRANGE
I am trying to query values from a different sheet using a range from the current sheet. Is there a way to simplify the formula and get rid of all the ORs? Like maybe using a FOR loop?
=QUERY(...
1
vote
0answers
7 views
Conditional Formatting formula for alternating checkerboard with Merged Cells?
I wrote a formula that attempts to create a checkerboard pattern via conditional formatting, that should even work with Merged Cells. However, it seems to fail in an almost undebuggable manner with ...
1
vote
1answer
11 views
How to reference a local cell in QUERY?
I just about have formula 100% perfect. Just one issue:
Formula (Works):
=QUERY( ImportRange( "https://docs.google.com/spreadsheets/d/1Lf4r_b0fmFh6fClZsGmfZmo8ZD87qf8N_0f-Jaoyu-g/edit#gid=0", "...
2
votes
1answer
14 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
...
1
vote
1answer
18 views
Array formula for adding letters after duplicate numbers in order
The below formula adds a letter after a sequential number, A, B, C, D, etc for the same number until it finds a new number, then it starts again from A.
I need help on turning this into an array ...
1
vote
1answer
21 views
How to extract the first rows that have a unique value in specific column in Google Sheets?
The unique items in D are: 4,3,2.
I wish to extract 3 rows for each D value, For each row, provide the first row that this D value appears.
I tried using =query but it doesn't support grouping or ...
0
votes
2answers
21 views
Format Cells if greater than “value of the cell”
Values of K11–K13 are variables. On the Conditional formatting, I want to use the output from those cells in the "Greater than" function. Typing =k11 doesn't work. I'm not really sure on how to do it. ...
1
vote
1answer
12 views
Which filename extensions are NOT safe to move outside of a syncing Google Drive folder? (because the corresponding Google Docs will be deleted)
When syncing Google Drive to your computer, the following SHORTCUT files will be synced, which link to the online web-based Google Docs documents...
.gdoc
.gslides
.gsheet
.gdraw
.gtable
.gform
...
0
votes
2answers
19 views
How to extract email link text from Google Sheets hyperlink? [closed]
Can you extract the copy email address text from a hyperlink? See example:
0
votes
0answers
10 views
How to getFullName()
I would Like a code example how I can get getFullName(), in google sheets.
Basically, I have multiple users using Google sheets and would want that every time they edit I cell in the Sheet, there ...
1
vote
0answers
11 views
Google Sheets' IMPORTXML pulling from the wrong region
I'm using IMPORTXML to get the price of an electronics component from Mouser:
If the prices are refreshed as a result of me editing the spreadsheet, it requests from my computer, and results in the ...
0
votes
0answers
33 views
Split Master Sheet into Multiple sheets
I have a master sheet where the data is like this:
Would it be possible to split this into multiple spreadsheets so it can look like:
and the sheets are divided into Weeks?
Scripts or formulae are ...
0
votes
0answers
15 views
How to move row in Google Sheets after checking a check box?
I'd like to be able to move an entire row from one sheet to another after a check box is checked. In this spreadsheet I'd like to be able to check the "Letter Sent to Client" checkbox and have the ...
1
vote
1answer
16 views
Escape single quote in cell content in a QUERY
My Problem
I have a Google Spreadsheet with two tabs - one containing regexes and values:
And the other containing phrases which matches regex in the first tab, and a QUERY Formula that picks the ...
0
votes
0answers
10 views
Using Google Sheets Making Leaderboard System [closed]
I'm looking to make a leaderboard where I can keep track of rankings for a game. Participants will play matches and the winner and loser will win/lose points based on a base amount +- the difference ...
0
votes
0answers
11 views
how to visualize time range data in google sheets
i have several thousand rows of data. Each row represents a task and includes the start time, end time, and data related to the task in the other columns. A task duration varies between 1 second to ...
-1
votes
0answers
16 views
Trying to calculate two time based intigers from same pool of resorces [closed]
I have a pool of resources. Two time factors are eating into them, consumption & spoilage, i.e.:
consumption 20 seconds for a unit
Spoilage 15 minutes per unit in a stack (30 units per stack, ...
0
votes
0answers
20 views
Function GetRange not found in Google App Script
function importCSVFromGoogleDrive() {
var file = DriveApp.getFilesByName("data.csv").next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
var sheet = SpreadsheetApp....
0
votes
1answer
7 views
Sliding window average of last N non-empty non-blank cells in Google Sheets
Similar to this question:
Average of last 7 non-empty non-blank cells in Google Sheets
I'd like a formula to compute a sliding window average of the last N non-blank cells in a column. e.g. For N = ...
0
votes
1answer
12 views
Assigning a value to a name from a list
I have items that I'm selling, and I've named these codes with the item I'm selling. I want to give these codes the price they should display. I've already got a formula to show the product of the ...
0
votes
0answers
20 views
IMPORTFEED Convert date format
I have a Google sheet that imports an RSS feed using IMPORTFEED and also the date of each entry using "items created"
=IMPORTFEED("url", "items created")
The date is shown as Sat, 13 Jul 2019 03:17:...
-3
votes
0answers
19 views
Google sheet , Get Name function? [closed]
Does anyone know how to get Name from Google Account and set it to a specific sheet and Cell?
Iv tried a lot of code but nothing seems to do the trick.
Any help will be greatly appreciated.
