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
1answer
12 views

Why does this Sheets Query Work?

I've been trying to learn the Query function in Sheets, and cannot understand why the Query below works. To me, it looks like there would need to be one more " to close the SELECT clause, but it ...
0
votes
0answers
16 views

How to track emails with specific subject line on Google Sheet

Attaching screenshot for reference how I required, it would be great if you can help me.
0
votes
0answers
19 views

Guideline for converting a pivot table to a query in Google Sheets?

I'm running into limitations as to what a pivot table can do. Is there a ready guideline for converting a pivot table to a query? Alternately a set of examples might do.
0
votes
0answers
27 views

Google Sheets pivot table won't show totals for more than one Row item

Edit: Here's a link to the original sheet read only. The Experiment tab is the one in question. If you want to play, you need to make your own copy, then share back in your answer. https://docs....
0
votes
0answers
22 views

Checking if a value is within one of many possible ranges

I have the following table, with two rows. The first value in each row is the "minimum difference" between terms. The rest is a series of numbers at least that far apart from each other, but ...
0
votes
1answer
21 views

Converting Date/Time string to Date/Time Value using Google Sheets

so I'm confused as to how to take the auto-populated string "01/18/2021@19:00 (CST)" into a valid Date/Time format. I know that I should be using Date formula but am unable to program this ...
0
votes
1answer
29 views

How can I reverse columns in a spreadsheet?

I have a google sheet with columns A->Z. I would like to reverse the actual columns without having to manually move each column one by one. Is there a way to do this?
0
votes
1answer
17 views

Export website privately from Google Sheets

I have a company Google Sheets workbook which ideally I'd like to share internally as HTML; at present it's shared as a .pdf which is OK but imperfect. To do so, I use the link sheets.google.com/etc/...
0
votes
1answer
16 views

On Google Forms, can someone edit their response but can't add a new one?

For example "someone 1" edits their response via the link that I have added in the Google Spreadsheet (https://drive.google.com/file/d/1gtULaR5VEfuLOP4f73Z3KofsnksmpSid/view?usp=sharing), ...
1
vote
1answer
12 views

Generating Google Spreadsheet Links from cell contents

You previously answered a part of my question, but I am looking to modify this behavior in multiple rows of column A so I don't want to manually correct it. In A2 I have: /mylist/groceries/index.html ...
1
vote
0answers
64 views

Create circle packing charts in Google Sheets

I am trying to find a way to add a circle packing chart to a Google Sheet, using the data in a Google Sheet as its data source. The only possibility I have found is this from DHUMANITIES http://...
1
vote
1answer
30 views

How do you dynamically add multiple URLs into rich text for Google Sheets using Google Apps Script?

I have multiple links to Google Drive files in an array. I need to get the links into this format in the cell: File 1, File 2, File 3 The links should be "File 1", "File 2" and &...
0
votes
1answer
26 views

How to get AUD price of bitcoin at a particular date using GOOGLEFINANCE

I am trying to find out how to get the price of bitcoin, ethereum, litecoin and bitcoin cash in AUD at a particular date. I have a table as follows +------------+-------+ | Date | Price | +------...
0
votes
1answer
28 views

How to show blank cells instead of 0s in Google Sheets?

I have 4 empty cells, and a formula in 4 other cells that show data from those cells. The formulas look like this - =IF(B5 > C5, B5, C5) =IF(B5 > C5, C5, B5) ==IF(B7 > C7, B7, C7) =IF(B7 >...
0
votes
1answer
37 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 ...
2
votes
1answer
28 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
25 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
1answer
27 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 doesn't seem to be working.
0
votes
1answer
24 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 ...
1
vote
3answers
60 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.
0
votes
0answers
19 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
34 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
19 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
20 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
14 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
43 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 ...
2
votes
1answer
60 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
34 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
8 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
22 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
26 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
18 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
1answer
20 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 ...
0
votes
1answer
30 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
28 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
26 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
48 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
46 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
29 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 ...
0
votes
1answer
28 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
46 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
16 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
84 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
35 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
28 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
49 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
23 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
41 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
35 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 ...

1
3 4
5
6 7
133