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.

Filter by
Sorted by
Tagged with
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):...

1
2 3 4 5
131