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
0answers
26 views

Google App Script to Hide & Show Rows Based on Dropdown Selection on Active Sheet

I am trying to create code that will automatically hide or show rows depending on the selection of the dropdown on whatever sheet is currently active. The drop down selection is a list of all the ...
0
votes
1answer
34 views

Converting multiple columns to list

I'm currently using Google Sheets and I'm capturing data with Google Forms. What I'm receiving from Forms, inside the corresponding sheets is an entry with timestamp, clientID, item1, quantity1, ...
0
votes
0answers
16 views

ArrayFormula / SumIf for multiple columns based on dymanic date / Monday through Friday

I have several form responses sheets (that I ImportRange into a common workbook) that need to display a total every Monday through Friday of the week that updates for current week Monday - Friday. I ...
0
votes
1answer
20 views

ImportRange and Query Function Data is condensing into one cell

I am trying to create a spreadsheet that imports data from one spreadsheet into another and then sorts that data by a key phrase. Here is the formula I am using: =Query(Importrange("URL","...
2
votes
3answers
59 views

How to prevent Google Sheets from reformating input from Google Forms?

I need to create Google Form where users can submit different data. Problem is that some of them use comma and others use dots as decimal separator and my Form should handle them both. Problem is, it ...
1
vote
1answer
24 views

Add Number of days and time on a datetime field

I have the following sheet: | A | B | C 1 | 23/2/2020 12:00 | 5 | 2:00 2 | 12/4/2020 13:00 | 2 | 2:0 And I want to calculate how many days after and how ...
0
votes
2answers
23 views

Working With Durations In Google Apps Script

I'm trying to write a function in Google Apps Script to manipulate durations calculated in Google Sheets. The cells holding the durations look like: 18:40:00 26:42:01 When I pass a duration into my ...
1
vote
1answer
22 views

Can someone help me with this google sheets group by query?

I have 3 columns. I would like to sum up the total revenue of Revenue by month in Dec-2019 (As you can see there are 2). And also sum up the Net profit by month(As you can see there are 2), last but ...
1
vote
1answer
27 views

Find value in 2 different sheets

So, as you can see from the example google sheet below, is there any way to have a formula in which when you try to find the value from both sheets (Example 1, Example 2; if lets say in Example 3, ...
0
votes
0answers
20 views

Combining Duplicate Data Within A Table? | Google Sheets

Table Data: This is a dulled down version of the Data Table I am trying to filter - which is a massive list of leads. Basically, I am trying to find a way to combine all rows with similar contact ...
0
votes
1answer
15 views

Optimize Google Sheets to process/summarize large amounts of data [duplicate]

I have a Google Sheet where data is uploaded from a Google Form in one page and workers input data directly in another page. The data is combined into one table via scripts. I am trying to create a ...
0
votes
0answers
23 views

How to check if there is any Data Validation error in the whole spreadsheet?

Is there a way to check in a Google Sheets spreadsheet, if there is at least one cell violating a Data Validation rule? If such functionality is not built-into the app, how can I write a Google Apps ...
1
vote
0answers
13 views

Adding new rows when using IMPORTRANGE [duplicate]

I have a spreadsheet as follows: A B C D E F 1 steve 12 xxy yellow 12 a1 2 david 32 yyx green 165 b1 3 tom ...
2
votes
2answers
64 views

Set calculation precision in Google Sheets

Google Sheets uses some sort of floating point arithmetic in its internal calculation, even if only integer numbers are in the calculation. For example: A1: =128^128 A2: =A1+1 A3: =A2-A1 Then, A3 ...
0
votes
2answers
86 views

Google Sheets If else formula

I'm trying to write an if else statement in Google Sheets. I want the value in cell B2 to dictate what the value you will be in cell D2. If the cell B2 is less than 12 than I want D2 = 17, if B2 is ...
0
votes
0answers
29 views

Importing a Google Sheets range based on checkbox value

I want to IMPORTRANGE of all columns B2:C if the respective A column is TRUE (a checked checkbox). How can I do this?
0
votes
1answer
39 views

Count Occurrences of Unknown Values

I'm really new to Google Sheets and formulas in general. I know you can count occurrences of values when you know what you're counting e.g. the times red is written in the column and you can search ...
1
vote
0answers
25 views

How to sort a UNIQUE()-generated list based upon values from an independent column? [duplicate]

Can and how do I get the UNIQUE()-generated list in column B to automatically sort (in A->Z order) based upon the numbers in column C, as shown in the image below? I want this "unique sort" to ...
0
votes
1answer
22 views

How to import cell data from one workbook to a different workbook?

All answers I have reviewed say use IMPORTRANGE by referencing the "KEY=" in the URL of the source cell. I find no "KEY=" in the source URL. So I always receive a "parse error."
-1
votes
1answer
19 views

I would like to use conditional formatting with references to another sheet

In a sheet of a Sheet doc, I would like to use conditional formatting : Which applies to my sheet1, range E8: UH49 Custom formula: = OR (COUNTIF (Holiday1; E5); COUNTIF(Holiday2; E5)) With Holiday1 ...
0
votes
1answer
18 views

How best to handle a range moving between getValue and setValue?

I have a simple experiment to illustrate the issue: function testRangeAddressAfterSort() { var spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); var testSheet = spreadSheet.getSheetByName("...
0
votes
0answers
17 views

Always display formula result

So I have this code formula on many cells in the sheet: =iferror(vlookup(D877,'Import Approved Video Stats'!A:L,5,false)) to import some info from other sheet. What do I have to add to that ...
0
votes
0answers
21 views

Fastest way to filter and sort data in Google Sheets

What is the fastest method of filtering and sorting data in Google Sheets? Not the fastest to set up, but the fastest to run. Another way to put it would be which method uses the least processing ...
0
votes
1answer
110 views

Rich text formatting in a Google Sheets cell

How is it possible to enable "rich text formatting" inside a single Google Sheets cell? I'd like to have a cell containing some text with both bold and non-bold content, words in italic, with some ...
1
vote
0answers
46 views

Create new sheet with a “template” based on cell value

I'm trying to do run some stats on the AFL (Australian Football league) here in Australia and want to get some better understanding on players form based on some key stats.= In order to do this, I ...
2
votes
0answers
55 views

Sheets QUERY (Select, Where, Contains, Limit) that works, except when blank cell

I have a Google sheets QUERY formula that pretty much works: =query(data,"SELECT A WHERE B contains '"& A5 &"' LIMIT 10",1) Essentially what the query is doing is, for the given data range, ...
0
votes
0answers
13 views

Ask Google sheets formula or script to remove duplicate sentence not duplicate word in cell text

I have text content in google sheets cell like this: Desciption : RGB (red, green, blue) colored LED lighting bulb - 16 colors total. Can be used in a single static (fixed) color or simply press the ...
0
votes
1answer
14 views

how to get max high of the given days excluding weekly off days

Can any one help how to get maximum high of given days excluding weekly off days (sat/sunday) from google finance. I am using formula like: =query(GOOGLEFINANCE("NSE:"&Y3,"high",(TODAY()-55),...
-1
votes
1answer
26 views

Google Sheets - If? ISBLANK? Not sure how to pull this one off

I am trying to find a formula for the following: If cell A1 has a date in it, copy that date to cell F1. If A1 does not have a date in it, copy the date from cell B1 into cell F1. I have a receipt ...
0
votes
0answers
7 views

Increase line spacing in Google Sheets [duplicate]

I am using Google Sheets and want to change the line spacing of text within a cell. cannot seem to find any information on this.
0
votes
1answer
29 views

How to transfer a cell value to another cell by pressing a button

I want to be able to click a button and the value of one cell gets transferred to another cell, and the new cell adds them up. example: If I put 5 in B6 then click the button, C5 should update to 5 ...
0
votes
1answer
116 views

Google Sheets - Historical Data not returning yesterday

The formula below is been used to get the historical data until today: =GOOGLEFINANCE("GOOG","ALL",DATE(YEAR(TODAY())-1,1,1),"12/05/2019","DAILY") Unfortunately I don't receive today's data (until ...
0
votes
0answers
18 views

Concatenate multiple columns automatically [duplicate]

I have three columns A B C D E F G H I I want to write a formula such that it will combine the three columns, one on top of the other such that my fourth column is: A ...
2
votes
1answer
25 views

How to access the “unformatted” date in Google Sheets?

I am working with a spreadsheet that includes a row where each cell contains the date of the first day in each month in a series of years: 1-Jan-18, 1-Feb-18, 1-Mar-18, ... , 1-Dec-18, 1-Jan-19, 1-Feb-...
0
votes
0answers
15 views

New solution to identify BY as a column label instead of an identifier?

I'm new to this, so I've posted this question as an "answer" to this post QUERY error for BY column a previous solution for this issue no longer works, and I need a new way to tell google (via code) ...
0
votes
1answer
28 views

Multiple Unique Values Across Worksheets in Google Sheets

I rarely use spreadsheets, but I thought creating a workbook for a contest would be really helpful. I am not sure if what I am asking is possible without a custom function. I am grateful for any and ...
0
votes
1answer
23 views

Determining location of checkbox that activated a script?

I have hundreds of checkboxes in multiple sheets on a single Google Sheets project. When a user clicks a checkbox, I want a script to activate. In that script, I need to know what cell the checkbox ...
-1
votes
1answer
6 views

IF THEN Google Sheets - referencing different workbook

I'm hoping to do the following: Spreadsheet 1, cell A1: pull in data from cell A1 in Spreadsheet 2, but ONLY if cell B1 contains exact text of "partner". Is this possible?
1
vote
1answer
24 views

How do I use conditional formatting on one column based on certain characters in another column?

When column D has a hyphen (-) in it, I want the fill color in column H to change. What is the custom formula for that?
0
votes
1answer
44 views

Generating a list in Google sheets with checkmark filters

I've been wracking my head over this for a few days now, basically I tried to use a index/match/if/countif setup to try to get a unique list generated based on filters made by checkboxes. (not ...
0
votes
0answers
17 views

How to auto expand column with formula to bottom of sheet

I am making a credit card debt paydown sheet that I want the user to be able to add more rows and have the formulas automatically fill down the column. I tried ARRAYFORMULA but because the results of ...
0
votes
1answer
15 views

Google Sheets apply conditionnal formatting for each column

I'd like to highlight the max value in each column. Example : A B C 1 50 12 60% 2 20 84 50% 3 38 20 15% Here A1, B2, C1 will be highlighted. So I know how to do that for 1 ...
-1
votes
1answer
25 views

google sheet need formula to go backward directory of URL link

example i have data in colum https://www.mybookStore.com/images/products/80/1020/40422/2/279/howtocookegg8.jpg https://www.mybookStore.com/images/products/80/1020/40422/2/279/howtocookegg9.jpg i ...
0
votes
1answer
65 views

How to open Google docs in new tab in Firefox

When I click a Gdrive item in Firefox it always opens in the current tab. How do I get it to open in another tab. When I click on any other URL it opens in a new tab why not Google docs? In Chrome ...
0
votes
1answer
21 views

How do I prevent float/decimal numbers from converting to text in Google Sheets? (iOS)

I have a cell with the value 2.2 that is being displayed as text. I’m unable to convert the value format from text to number. Screenshot 1 shows that cell format is number Screenshot 2 shows that ...
0
votes
1answer
24 views

Using Google Apps Script in with arrayformula?

I'm using an array formula in Google Sheets to determine when a cell shows 'Expired' based on date. When the cell shows Expired, I've a script which should transfer rows with Expired to another sheet....
0
votes
1answer
26 views

How can I make some data on a Google Sheets auto-sorting but in different columns in every sheet?

If i want to sort different columns in every sheet, what do i have to change? function onEdit(event){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); var editedCell = sheet.getActiveCell(); ...
0
votes
1answer
16 views

IMPORTXML not working [duplicate]

I’m trying to scrap prices from various e-commerce site. However the IMPOrtxml function is not working For instance, https://shopee.sg/Ice-Silk-2019-Men-Underwear-Cool-Breathable-Comfortable-Sport-...
0
votes
0answers
7 views

Creating a Scatter PLot with Equal Axis Scale

When creating a Scatter Plot in Google Sheet, the X and Y axes get scaled independently. Is there a way to set them to always use the same scale/units? Basically, such that given a point (10,10)...
1
vote
1answer
48 views

Google Sheets - If cell contains text that matches text from a column, return another cell

https://docs.google.com/spreadsheets/d/1GG7ekrD6CxxpowrRDm9DMvv3xHdvtAa8MHYCk4gI4D4/copy 1) If D10 (or I10 or N10) matches an exercise from the corresponding column to the right, return another cell (...