Questions tagged [google-sheets]
For questions about Google's web application to create and edit spreadsheets on a web browser or app. 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 chance that you get a satisfactory answer.
6,510
questions
0
votes
0answers
7 views
Google Sheets. How to combine a checkbox and conditional formatting?
Here's my problem.
I have a cell with a checkbox. I can make it turn green when I check it. But I want it to turn green if no conditions are met and turn orange when a condition is met. The condition ...
1
vote
1answer
9 views
How to automate find and replace link in google sheets from form photo submission
Trying to automate find and replace function on form submission within sheets.
Principal/Teacher submits a photo through a google form and I want to change from the file link to a search to link ...
0
votes
1answer
9 views
Sort GS table by column values into new separate tabs
What I am trying to achieve:
I have a JIRA table output containing (aside of other columns) task name, story points and logged time.
Story points are meant to be a Scrum story point which defines ...
0
votes
0answers
13 views
Transposing a data range and replacing a column with query
I have a data set that looks like the one below in the image. And I'm trying to turn it into the expected table in the image as well. A simple transpose doesnt seem to be working.
0
votes
1answer
6 views
Google Script: Changing date format from Typeform Response in Sheets
I'm using Google Script to add a function where once the response from Typeform is sent to the sheet, a button called "create document" is added to fill up the Google Doc template using the ...
0
votes
2answers
21 views
How to use counta function with two columns (or)
I would like to use counta function to count rows that are not empty based on column A or B.
In other words.. if either column A or B not empty, it should be counted.
1
vote
0answers
14 views
jump to cell using dropdown list data validation
I'm trying to create a data validation/dropdown list to jump to a specific cell.
I have a large spreadsheet that I need to navigate around, and it is client facing. I'd love to have a dropdown list ...
1
vote
2answers
18 views
Transpose multiple columns into single column
I have a Google sheet in which the rows are unique cities and the columns are years. It can grow in two dimensions.
| | 2010 | 2011 |
------------------------...
0
votes
1answer
10 views
IF, THEN Google Sheets formula help
I'm having trouble figuring out the right formula for an if, then scenario in Google Sheets. I have two tabs of separate spreadsheets open in one Google Sheet. I need the second tab (Cost Analysis) to ...
0
votes
1answer
10 views
How can I calculate the difference between two time fields, being the second greatest than the first one?
I'm having trouble getting negative hour as a result. For instance:
G H
08:30 06:00
I would like to get a column with the difference between H and G. So I would be filled with -02:30.
...
0
votes
1answer
9 views
Transpose Time Google Sheets
I love the transpose date function above. Can you do the same thing with time in hour increments? If someone checks in at 2pm and checks out at 7pm, the cells to the right would count 2pm, 3pm, 4pm, ...
0
votes
2answers
22 views
how to do math in google sheets relative to a cell?
I'm working on some things relating to properties of one dimensional cellular automata, and am trying to make a function in which the value of a cell in column A is equal to the sum of all other cells ...
1
vote
1answer
31 views
How to split two-line rows into separate rows
Editable spreadsheet here:
https://docs.google.com/spreadsheets/d/1cHf9zC00LymZgb17J746ZUoD-YRG1UvIUQQXVlovBrE/edit?usp=sharing
You can see each non-blank row contains two lines. I'm trying to find a ...
1
vote
0answers
28 views
Google Sheets split into new rows while duplicating surrounding row entries
I am creating a spreadsheet for a service drive my non-profit is doing. As people sign up through a Google Form, the information comes in like this:
However, to make it easier on our volunteers, it ...
0
votes
0answers
7 views
Google Sheet script will not run on a tab linked to a Google Form
I wrote a script to move a row of data from one sheet (Sheet1) to another (Sheet2) when I type the word Yes in column one. I can get the script to work perfectly when the sheet is not collecting Form ...
0
votes
1answer
18 views
Search through multiple tabs and pull results
Test Sheet for my use case: https://docs.google.com/spreadsheets/d/1_gcyygG8IhVc-4mXf8n09IFG5ZIYuK_UxWxPLQ5av-k/edit?usp=sharing
I've had some input from another user on the site helping me search ...
1
vote
1answer
25 views
Calculating a total value based on X periods, that takes the previous period into account, without intermediate calculations?
I'm trying to create a simple Customer Lifetime Value calculation in Google Sheets that takes 4 variables:
Income first year
Retention rate (% chance of being a customer the year after)
Discount rate ...
0
votes
0answers
17 views
Possible to color code text based on JUST the text value and automatically?
I've been wondering about this for years... it would be such a life-changer.
Let's say I have a table:
Spectrum Bills
XYZ Rent
ABC Food/Beverage
Spectrum Bills
is there any way ...
1
vote
1answer
12 views
Count number of rows with a specific MONTH in the date of a specific cell and a Boolean condition in another cell
Let's say I have a number of dates and whether they occupy full or half a day:
DATE | WHAT | HALF?
...
2021-4-1 | Something Something | false
2021-4-2 | Something Something | false
...
0
votes
0answers
15 views
How to get URL from Block Text in Cell in Google Sheets
I have a Google Sheet with Text in A1:
Welcome to my Website!
Visit some Amazon Products: https://amzn.to/3o7UGKS
King regards!
So what I'm looking for is how to get the link Amazon Link from the ...
-1
votes
0answers
13 views
Want to add script editor in Google Sheets
I want to compare 2 cells i.e A2 and B2. In these two cells there is a timestamp. I want to compare if B2 is greater tha A2. I want the colour of C2 automatically changed from green to red. I want to ...
0
votes
1answer
26 views
Remove duplicate data
I have built quite a basic CRM system, each client has a sheet, on that sheet there's a form that populates information on rows related to what been inputted. the idea is to standardize information ...
0
votes
0answers
17 views
Appending current Share Price to Historic Daily Share Price on Google Sheet GOOGLEFINANCE Function Sparkline
I have a sparkline for the daily price for the last 30 days for a share in google sheets using the following formula (F1):
F1 =SPARKLINE(GOOGLEFINANCE("GOOG","price",Today()-30,...
1
vote
0answers
21 views
Google Form to sheet to Doc, with calculations after pushing to sheet
NOTE! I am not a programmer I'm just fumbling through this! I've gotten as far as I can with searches, getting lost on multiple steps as most responses don't integrate on thing to the next.
Background
...
0
votes
1answer
45 views
Combining if and or
I am having difficulties creating this formula in Google Sheets. I have columns D,E,F with the headers:
Should Actual Diff.
Under D and E a drop down with Top Expert, Professional, Working Knowledge,...
0
votes
2answers
30 views
How to filter Google Sheets by multiple contains using AND
I've searched for something similar but couldn't quite duplicate based on some other posts.
Column F has data on which websites you have a login with.
Let's say
F2 = Amazon, Facebook, Google, Twitter
...
1
vote
0answers
26 views
Google Sheets: Lookup Value that falls after a Timestamp
Need serious assistance. Please see images below for a reference.
I have 2 sheets - The second image represents a sheet that is connected to a Google Form. This inputs the site information for various ...
-1
votes
0answers
7 views
compare double with integer android [closed]
I receive two strings containing either an Integer or Decimal (Double) from GoogleSheets to Android.
String 1 is requirement and the other is available.
I need to compare both and take a decision.
if ...
0
votes
1answer
23 views
How tell Google Sheets to not change columns width?
I wrote a script that generates excel files. In those files, I add images inside cells. I set the width of the images cell by calculating the total images width.
Here is how it looks in MS Excel:
And ...
2
votes
1answer
34 views
Run script through multiple Google sheet tabs
I have a script which deletes columns of information that is now rendered obsolete.
However, I have multiple tabs which means going through them all manually, how can I edit my script to do this ...
1
vote
0answers
15 views
Adding Button to add points to another sheet - Basketball Live Scorebook
I have created a scorebook sheet in google sheet for tracking live basketball stats.
One sheet with the scorebook table for the team with each player one a row.
I want to have a separate tab for data ...
2
votes
1answer
55 views
How to filter based on multiple cases
I am aware that I may be asking the wrong question here. Not sure how to ask, but I am trying to create a budget on google sheets that to record how much I spend on each day. Right now I have the ...
1
vote
1answer
34 views
Excel: how to move “answers” rows into under column “questions”?
How do I move answers from other column to same question under another column, can any one guide the exact formula to achieve this? See example below what needs to be achieved as shown in picture.
2
votes
1answer
25 views
Script in Google Sheets which deletes every column that doesn't have a header
I'm trying to take an existing script but change it so that every column that is without a header is deleted.
function deleteColumns() {
var required = ["Name", "Position", "...
0
votes
1answer
40 views
Sum values across multiple Sheets IDs automatically by creating a formula?
I used this post to have all Google Sheets IDs in a folder
Getting all files' file-id from a folder in Google Drive
I would like to make a formula or script to sum values in table across all these ...
0
votes
1answer
20 views
Can you filter a column in size order of an imported HTML? I have tried using query with no success
Using the below importhtml I'm hoping to order the numerical data of the third column in size order, but it seems I have lost my way on the model I was following. Any help greatly appreciated.
=...
0
votes
0answers
31 views
I have a Google Form that feeds into a Google Sheet. I need to pipe that information into another tab/cell in a dynamic way based on a data validation
Lets see if I can explain this well enough...
I have a spreadsheet "Main_page" that is dynamically pulling information from a secondary sheet, "All_shots". I also have 2 google ...
0
votes
1answer
30 views
Sum over a formula in Google Sheets
I have a Google Sheet and I need to add a lot of stuff like
(ABS(A1)+A1)/A1+ (ABS(A2)+A2)/A2+...+ (ABS(A10)+A10)/A10
but I don't want to write out the sum each time for each term A1,A2,A3,..., A10
Is ...
0
votes
1answer
39 views
In Google Sheets, how would I go about finding column header of a filter result from another tab?
I have a simple form in one tab with a couple entries.
On another tab there is a filter that automatically populates array based on form tab.
Is there a simple way to reference a column header of a ...
1
vote
1answer
38 views
AVERAGEIF on last N rows
This seems pretty simple but I can't find how to do it.
I keep a Google Sheet where I add several times a day new lines as following :
| A | B | C |
| --- | --- | --- |
1 | John | 10 | ...
1
vote
1answer
20 views
Add '/' character in between a number (which should be formatted as a date) and turn it to a date
I have received a spreadsheet which has the dates formatted with only numbers without separators and I would like to transform them into a date format. For example, I would like to change the number ...
1
vote
0answers
14 views
Google Sheets: a cell calls an API for a changing currency price, but sometimes returns an error. How do I keep the last non-error price?
I'm using importJSON to grab a number that represents the current price of a currency. Sometimes, the API isn't available and returns an error message. Is there a way for me to record the last non-...
0
votes
1answer
24 views
Too Many IF Statements in Google Sheets
I am trying to get this formula to not come back with an error. As far as I know, I cannot make a VLOOKUP chart in Google Sheets because of it looking at multiple cells to give out the value. It ...
0
votes
0answers
21 views
Reading an address function in an offset function
I'm having trouble with the offset function, it doesn't seem to read address functions.
Prompt:
Read down the health column of the Monster Statistics by Level table until you find your monster's HP. ...
0
votes
0answers
19 views
Using WHERE VLOOKUP in QUERY function in Google Sheets
I have a big table which is composed of a bunch of date-stamped data sets (monthly reports). I want to compare products' metrics between last time it was updated and the time before that. However I ...
0
votes
0answers
6 views
How to conditionally lock the result of a formula in Google Sheets
I've got a formula that uses data that frequently changes.
Is there a possibility to conditionally lock the result, so that even if references of the formula changes, the result does not change?
So ...
1
vote
0answers
28 views
How to limit selection of dropdown list to single/multiple items based on value of a cell?
I have a spreadsheet of the list of patients daily.
Each patient has been classified in disposition column(Q) as Admission, Consultation or others...
Column R is Specialty. Column S is Services
There ...
0
votes
1answer
34 views
Sum Last 12 Columns In Google Sheets
I'm trying to track my recent 12 months spend on an ongoing basis. Each month a new column is added for the new month, but I want Column B to show the sum of the most recent 12 months.
I've set up a ...
0
votes
0answers
17 views
Get YEAR Month# MonthName from DayOfWeek
This results in a nice table of sorted weekday, number of those weekdays and total from values in column A like 01/01/21 and column D like 123
=QUERY(Query(A2:D, "select (A), sum(D)
...
0
votes
0answers
14 views
query: SELECT something WHERE [an array] CONTAINS something else in Google Sheets
I'm using Google Sheets as the backend for a no-code dev environment (Glide). I am trying to query an array rather than a single column.
Here's my formula that works for looking in just one column (L):...
