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.

Filter by
Sorted by
Tagged with
0
votes
1answer
10 views

Google Sheets formula

I'm using Google Sheets make a spreadsheet with the name in column a, the company in column b and in column f, I need their email. I have 3 companies, and they have slightly different email formats. ...
0
votes
1answer
14 views

I have a list of phone numbers entered into Google Spreadsheet in this format: 404-313-2974

How can I convert this entered cell into (404) 313-2974 ?
0
votes
0answers
7 views

Excluding 0 in color scaling

I have a list of numbers that I want to color scale, with the lowest being red and green being highest. But the list sometimes includes 0's, I don't want the 0's to be included in the color scaling ...
0
votes
1answer
14 views

Help with a Google Sheets formula for Inventory Tracking

Wanted to get some help with finding the correct formula to input into a cell in google sheets. I need to determine when we are getting low on product and when it is recommended to order more. I ...
0
votes
1answer
11 views

Problem with Nested IF() statement on google sheets

I am trying to create a formula for a monthly expense report using the following nested IF() statement: =IF(B2="Weekdays", C2*20, IF(B2="Monthly", C2, IF(B2="Weekly", C2*4, IF(B2="Bi-weekly", ...
0
votes
0answers
31 views

How to recall a PDF attachment sent from Google Sheets

How do I retrieve a PDF attachment sent from Google Sheets through Gmail?
0
votes
1answer
16 views

Google Sheets - search multiple columns for specific text

Info added after as response to first comment: I just realized that the IF(PAGE1!C5="OPTION1" probably wont work, probably because I haven't described the wish good enough. I will describe with this ...
1
vote
1answer
25 views

Convert ARRAYFORMULA output from 2D to 1D range

tl;dr How to create an 1D range from a 2D range output of an ARRAYFORMULA. Intro I have a Google Sheet that uses a very complex ARRAYFORMULA to perform data validation on about fift cells in a row. ...
0
votes
1answer
15 views

How can I get to the bottom of a Google Sheet

I want to open my Google Sheets and go automatically to the bottom/last line entered as there are over 2000 lines. I have entered the below text in: tools > script editor, as seen in another post ...
0
votes
1answer
23 views

Is there any way to set different formulas for multiple ranges all at once?

In my question Is there any way to set different values for multiple ranges all at once?, I found out how to set different values across dis-contiguous cells all at once, and we developed a good ...
0
votes
1answer
58 views

How do you interpolate data in a horizontal range that's non-linear?

I've been told what I'm hoping to do is called 'spline interpolation'. I've imported monthly youtube channel viewership figures from google API. And I've layed out the dates and views in a horizontal ...
-1
votes
0answers
22 views

How do I get the row number of the last non-zero value from a range in Google sheets?

I have a column of numerical data, say a1:a20. I want to search that range and return either the cell address of the last non-zero, non-empty cell, or if that can't be done, the row number (since I ...
0
votes
1answer
21 views

Change cell colour based on another cell containing text

I am new to Google Sheets and trying to create a custom formula in conditional formatting. Looking to see if someone would be kind enough to help. The picture below is a sample size of two list of ...
0
votes
1answer
33 views

Copy cell info on account of cell matching specific te

Edited as response to comment: I had to copy data into a new sheet, which is why it doesnt entirely match your answer. Here is where Im at now: attached screendump is new. Hope you can help with ...
0
votes
1answer
29 views

Incorrect and somewhat strange results when using COUNTIFs with Arrayformula

I have three functions responsible for checking if I own a stock (by seeing that the column F is not empty) Then I want to check column AB to see what market the stock is from. In the first case I ...
-1
votes
0answers
43 views

How to combine specific hours with fees in Google Sheet (dates + cycles + fees)

I’m trying to prepare a sheet to count fees based on cycles (specific hours) to know how many time I got to do some kind of action. For example, I got a virtual pet which has maximum 1 500 000 ...
0
votes
1answer
19 views

Google sheets - “Anchor” formula to pivot table data which keeps moving

Ive got a Pivot table in a Google Sheet which summarises some numerical data by groups. Ive attached a screenshot of the pivot table below. I want to be able to sum the values of the "grand total" ...
0
votes
0answers
9 views

Scatter Chart with Equal Axis Scale in Google Sheets

My data for X and Y uses the same units. How can I get a scatter chart to force the same scale (units) for both X and Y axis? In Matlab this would be called equal axis.
0
votes
2answers
27 views

How to get the average of times while skipping over empty cells?

I'm trying to make a formula that looks over all of the times and returns an average of the time but skips blank cells. I'm really close, just can't get it to skip over blank cells. Here's what I ...
0
votes
1answer
13 views

Is there a way to sum a bunch of IMPORTDATA() outputs?

I am attempting to sum quite a few different ranges of IMPORTDATA() I want to do something like: =SUM(N9:N14,N16:N21,N23:N36,N38:N53,N55:N106,N108:N113) My issue is it can't sum up a bunch of ...
0
votes
0answers
19 views

Added importJSON function and importHTML stopped working

I built a great Google Sheets spreadsheet which has been working for a year with no hickups. It pulls data from websites using importHTML. I then tried to add the importJSON function to the ...
0
votes
2answers
27 views

Conditional formatting custom formula not working in google sheets

custom formula is: "=COUNTIF($B1:$B100,"month")>1." I tried also: "=COUNTIF($B1:$B100,"month")>1" =COUNTIF($B1:$B100,"month")>1 ="COUNTIF($B1:$B100,"month")>1" I know it shouldn't work ...
0
votes
0answers
19 views

Query Import Range returns two rows

I've been using =Query(Importrange( functions for quite a while now and something I can't figure out is why the import is returning with two rows. Here's the formula I've been using: =QUERY(...
0
votes
1answer
11 views

Moving Rows of Google Form Responses to a new Sheet based on a specific answer in a column

I have a Google Sheet collecting responses from a Google Form. Based on answers in Column C (Col3), I would like the entire row imported to a completely different Google Sheet. I figured out a ...
0
votes
0answers
8 views

Google Sheets opening tabs

We have set up a google sheet that has each month with a different tab, we want to open the current month, what is the simplest solution as the main user is not very computer literate.
2
votes
1answer
28 views

How to automatically calculate averages from ID in Google Sheet?

I've been looking around for an answer but couldn't find what I was looking for. The problem is that I want to use an arrayformula, but can't figure out a dynamic formula that: Looks at the unique ...
0
votes
1answer
21 views

Possible for users to have unique instances of the same document? (not downloaded copies)

My Google sheet consists of a locked list of items and a single cell that can be edited. This cell acts as a search box to filter the list. I want multiple users to be able to search at the same ...
0
votes
0answers
14 views

How to translate aggregate formula from excel to Google Sheet

I am kinda struggling now. I need to make this formula from excel works in Google Sheet. =INDEX($C$5:$C$24,AGGREGATE(15,3,($B$5:$B$24=$M$8)/($B$5:$B$24=$M$8)*(ROW($B$5:$B$24)-ROW($B$4)),ROWS($A$5:A5))...
0
votes
1answer
16 views

How to read a long text in a cell that's a result of a formula?

I have a text cell that needs to in the "Clipping" wrap mode, cutting most of it away from view. Or is at the end of the range. Or is too long to be displayed. Normally, I would read the cell by ...
-1
votes
1answer
66 views

How can I speed up this script in google sheets?

I am wondering if there is a more efficient way to speeding up this script? var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trends"); var wsOptions = SpreadsheetApp....
0
votes
2answers
15 views

Convert from duration ( HH-MM-SS) to minutes

I want to convert from duration ( HH-MM-SS) to minutes, but the result return only correct when duration of hour < 24 hour with function HOUR(). Otherwise result is not correct. Anyone can help? ...
0
votes
0answers
27 views

Google Sheets automated proper case?

I'm playing the game World of Warcraft and my guild has an attendance signup sheet for raids we do every week together. Everyone visits the sheet and types in their class|name|talents so that the rest ...
0
votes
1answer
13 views

Google Sheets data validation help

I am trying to do this simple thing in Google Sheets with data validation, so what I want to do is not allow people to put negative numbers and numbers with decimals. I've been trying a lot of ...
1
vote
1answer
16 views

I have the formula I want to use but now sure how to change it with time another time zone

I have a formula setup that I want to use. But I have a separate sheet that I want to use in a different time zone. The one I have written is in EST. but I want another sheet to use the same formula ...
1
vote
1answer
12 views

help with query finding multiple entries in 1 column

I am trying to query several different colors from one column. This is what I have so far: =query('Raw Data'!C1:J1000, "Select E, D, I, J, C, F, H where H contains 'Monday' and F='Green' ...
0
votes
1answer
21 views

Is there a way to conditionally format border styles in Google Sheets?

The title says it all. I have some conditional formatting rules, it seems I'm limited to some basic formatting options; Is there any workaround to apply further formatting options (such as border)?
1
vote
1answer
12 views

Copy conditional formula to separate columns GoogleSheets

I've got a simple conditional formula to make cell green if there is more than X not empty fields. It works fine but I need a way to copy that formula to other columns. When I do it the simply click ...
0
votes
0answers
9 views

MINIFS using multiple ranges

I am trying to find the smallest positive value for multiple ranges. Essentially I want to find it in column J without including cells J7,J14,J21,J28 and J35. This is what I have so far: =...
1
vote
0answers
33 views

Custom function on Google Sheets that can extract hyperlinks from formulated cells

I recently found this post, which is so close to my solution, but not quite there! Extract the link text and URL from a hyperlinked cell The issue with this custom formula (=LinkURL()) is that it ...
0
votes
1answer
19 views

Conditional formatting row of data based off of a date range

I have a bunch of entries in a sheet which are all between a range of dates, the two columns with dates are start_date (B) and end_date (C). I already highlighted past time periods using; =DATEDIF($...
0
votes
0answers
17 views

How do I remove custom currencies in Google Sheets

Basically this but from the create screen where you create the coins Is it possible?
0
votes
0answers
15 views

Using Filter and Importrange

Can you use the Filter(Importrange to filter more then one column, for example, where C = "Foundations" and F = "S1". I cannot use query because I need links to come through on the import. My formula ...
0
votes
0answers
17 views

How do I copy a set of data items from another tab based on highlight conditions?

I'll just give you a brief idea of what I'm trying to do with my limited logic. I'm pretty certain this could be done in a much better way. This is for my Instagram account and I'm trying to do the ...
1
vote
1answer
37 views

Find and replace rich text within a cell

I would like to know if it is possible in the current sheets api, or the google script for sheets, to find and replace rich text. In my specific example, I have red text that I would like to remove ...
0
votes
0answers
41 views

Change interpolation javascript from vertical to horizontal

I would like to make this script work horizontally. This code is from another answer here, but I have not been able to make it work horizontally. I need to use it across columns in one row, ...
0
votes
1answer
26 views

Stacking time series in Google Sheet Combo chart

I can approximate a stacked time-series plot using the Combo chart option in Google Sheets: But it's not quite a "real" time series plot because the temporal gaps are not represented. Compare to this ...
0
votes
2answers
16 views

How can I make a formula ignore a blank cell in Google sheets

I am creating a budget template and have a formula that multiplies cells. However, not every cell will have a number in it. How do I make it so the formula will ignore blank cells or make them = 1 ...
1
vote
0answers
8 views

Merging Data on a Google sheet depending on the column header description

I'm relatively new to Google Sheets and I'm faced with a small challenge. I've created a long Google form which has caused multiple of the same heading on Google sheets. How do I go about joining ...
0
votes
1answer
17 views

Spreadsheets: How do I only select non-empty cells in function?

I use this function to find a random content of a cell: =index(A3:A100, randbetween(1, counta(A3:A100) ) ) Some of the cells in A are empty. How can I change this function so that only those cells ...
0
votes
0answers
9 views

Google Sheets: Mark checkbox if cell contain specified text

I want to mark a checkbox if adjacent cell contains specific text. Like for instance, cell C2 can have these values: Newspaper Magazine Website Facebook Cell c3 which is a checkbox, will be marked ...