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
9
votes
4answers
26k views

How can I group a pivot table into weeks or months?

In Microsoft Excel, in a pivot table, you can right click on the dates - while within the pivot table, on a date and select group (from the drop down menu) - then you can choose different groupings, ...
0
votes
2answers
528 views

Create a custom DateTimePicker in Google Sheets

In a sheet I have a column to enter a datetime value that would later be checked by a script to create a trigger. I would like to set a picker in that column to ease selection and avoid errors. I ...
0
votes
1answer
6 views

How to convert a rational number like 1.6 to the higher integer number?

How can i convert the number 1.6 to 2, 3.8 to 4 and 1.2 to 2. Any number that has0.0 to be converted to the next number . Round will round the number to the nearest integer but I want to the higher ...
1
vote
2answers
13 views

How to test apps script addon

I have created an addon using this guide. code.gs function onOpen() { SpreadsheetApp.getUi().createAddonMenu() .addItem('Start', 'showSidebar'); } function showSidebar() { const ui = ...
2
votes
2answers
37 views

How to sum a range columns from another google sheet

I get this daily data consumption spreadsheet report from a vendor that looks like this userid feb1 feb2 feb3 . feb29 u1 100 34 23 . 4 u2 0 24 ...
0
votes
1answer
25 views

How to use google apps script to validate a cell?

I have been trying to write this code to validate a range of cells. If the cells contain the text "true" then set value as "Incorrect Data" else "DD Data" /** @OnlyCurrentDoc */ function myFunction() ...
1
vote
0answers
8 views

How to solve the issue of my query results of in my sheet?

https://docs.google.com/spreadsheets/d/1VsEvt2KnYGnlN_qHjkjPohVHF5MUqfyHD7tSk0VpkqA/edit#gid=1418057437 I have this sheet that I enter all data of patients in (Data_Entry) tab and I want these data to ...
0
votes
2answers
847 views

Match word in a cell with a column of words

I need to match a word to a list of words and then insert the category name in the cell next to it. Eg. I will in Column A type a list of words like Apple, Red Apple, Coke, Carrot The formula must ...
0
votes
0answers
11 views

Jump to a cell, address of which is located in another cell

A1 has a formula that shows the address of the last row with data (A958, A959). I need to make a link in A2 to the cell address of A1. (eg. If A1 has Value C456, I need to jump to Cell C456 by ...
1
vote
1answer
3k views

How to combine FILTER with TRANSPOSE in data validation custom formula

Cells A2, C2, and E2 contain the data I want to make my custom list for data validation. Cells B2 and D2 have data I don't want in the list (just text - "vs" - in them). So, I'm trying to use the ...
1
vote
0answers
9 views

How do I create a list of ranked numbers in Sheets––one that will adjust if I change a number?

I have a column of numbers 1-50 in a Google Sheet, which are a ranking list for a different column of fifty items (items are ranked relative to one another, so 1 is the best in the list and 50 is the ...
0
votes
1answer
1k views

Using checkbox to copy data form worksheet A to worksheet B

I am trying to use the new Checkbox function in Google Sheets. I can't figure out how If I click the checkbox in A9 on Worksheet 2 I want the Text in A10 and the $ amount in A11 to be copied to cells ...
0
votes
1answer
23 views

Sort a column by name with query between tabs

I am pulling information from one tab to another and I believe this is why it will not allow me to sort (because it is not sorted in the original tab). The easy solution would be to sort that tab. ...
1
vote
0answers
7 views

How do I specify a consistent interval for a date-range axis in a Google Sheet Chart

I have a Google Sheet with a set of deposits and withdrawls and the running balance. I have added a chart to the sheet, so I can visualize what the balance has been over time and when deposits or ...
1
vote
0answers
14 views

Google Spreadsheet: Adding together multiple Sum Filter or Sumifs with multiple conditions across multiple sheets?

So I have a budgeting spread sheet. I have an overview page that I need to link to a "Checkings" sheet and a "CC-Chase" sheet. To calculate the amount spent on a specific category ...
1
vote
1answer
853 views

Looking for a “weighted average if” functions in Google Sheets

I am familiar with the =AVERAGE.WEIGHTED function in Google Sheets, but I'm wondering if there's a way to add an IF in there, like =AVERAGEIF. I have a baseball spreadsheet, with the following ...
0
votes
1answer
17 views

How to apply a conditional formatting rule on the last row of similar cell value across one column?

I am trying to conditionally format the last row that has similar values of sorted data based on country column. In this example of my test sheet, I'm sorting the data from A3:C18 by placing the ...
4
votes
2answers
13k views

Insert a linebreak in a Google Spreadsheet cell on an iPad

How does one add a linebreak in a cell using an iPad. I tried wrapping text but it wouldn’t work any other solutions?
1
vote
2answers
2k views

Google Forms Duplicating answers to Google Sheets

I have a google form to capture order information, When the user captured all order information, it pushes to a google sheet so we can keep an order history of who ordered what, when and how much. ...
0
votes
1answer
30 views

IF/AND functions from multiple cells

Would anyone know how to fix this problem? I want cell A676 to categorize based on info from 2 cells C676 and D676. Basically IF the word "Paypal" is in cell D, AND there is a specific ...
1
vote
1answer
14 views

How do I print a specific number of A based on a value from a cell in Google Sheets

There's a cell that would contain the number of times a letter would be printed in a cell. i.e. cell a1 = 3. b1 should look as "A A A" my rough script is: for(var arow=0; arow<4; arow++) {...
0
votes
0answers
11 views

Group By, multiple times?

I'm in the midst of creating some code to query my data. Here's an example of said data: Text version of image Name ToF AOF Jon Cena Buyer 1 Tim Brown Seller 3 Cabel Guy Buyer 5 Cabel Guy ...
0
votes
2answers
1k views

Problem with QUERY(IMPORTRANGE()) to retrieve data

I’m having a problem with the =QUERY(IMPORTRANGE()) formula while retrieving a column data from one spreadsheet to another. =QUERY(IMPORTRANGE("spreedsheetURL","Sheet1!A2"),"select Col2 WHERE Col1 ...
1
vote
1answer
93 views

Google-sheet IF function not working

I need a formula in Google spreadsheet that will: +1 when the value is >=5, +2 when the value is >=10, +3 when the value is >=15, +4 when the value is >=20, +5 when the value is >=25 ...
0
votes
1answer
8 views

How to match formatting of one cell in Google sheet with another?

I typed "2:30pm" into a cell in a Google sheet, and it formatted it as "2:30 PM". I typed "2:30pm" into a different cell, and it formatted it as "14:30". What I ...
0
votes
0answers
10 views

Remove duplicates but keep data based on several criterias

I have a database with thousands of rows and want to remove duplicates but also keep records from earlier in certain conditions. Here is my problem: Column A will have unique numbers for referals in ...
0
votes
1answer
197 views

Using Google Sheets cell reference in SOQL Query

I'm using Data connector for Salesforce Google Sheets add-on to pull Salesforce information into a sheet via SOQL queries. I'd like to use a cell reference from my sheet inside one of those queries, ...
64
votes
16answers
131k views

How to prevent cut and paste data changing references in formulas?

Google Sheets has a feature where if you have a reference to a data cell in a formula, and you cut the data from that cell and paste it in a new location, the reference in the formula is updated to ...
1
vote
0answers
14 views

Pull data from separate Google Sheet into a Google Forms response sheet and update dynamically

I have a Google Sheets leave sheet for employees where we track the leave days due and days already used. I also have a Google Form which submits responses to linked sheet and runs with Form Publisher ...
1
vote
1answer
22 views

Combine multiple IF statement/condition into one Google Sheet column

How can I achieve in Google Sheet so that the column will auto populate the correct "vendor" based on the selection on "model" and "size". Refer below: There will be 2 ...
0
votes
0answers
9 views

Change Values cell location from number of another cell

So I have =HYPERLINK("#gid=0&range=A2"; "TMI"&Status!B4&" - "&Status!C4&" - "&Status!I4). What I want to do is to change number 4, after,...
5
votes
3answers
6k views

How to use “aggregate” chart feature on Google Sheets

Line graphs and most other types in Google Sheets have a checkbox for "aggregate" in the setup tab. I've tried looking through the instructions but I can't find any documentation on how to use this ...
0
votes
1answer
26 views

Bar Chart Merge 2 columns in a single bar

I have the following table, I want to generate a bar chart where for each year I can see a single bar, including both Cash and Credit. Today I only see this: Link here
0
votes
0answers
14 views

Auto-generate unique sheets when you add a new row in Google Sheets?

I'm a fairly new user to Google Sheets, I'm trying to set up a sheet whereby when you add a new line in one sheet, it creates a separate check-list sheet. So when a new user is added to the master ...
3
votes
1answer
65 views

Calculate time difference within one cell

I'm trying to calculate the time difference and get the result 04:00. This obviously doesn't work because MINUS expects 2 arguments and instead gets a range. Generally, is there a way to output a ...
1
vote
1answer
101 views

Line chart with hourly-daily data

I have the following temperature data: 1/1/2019 9:00AM 18.5 1/1/2019 5:00PM 21.3 2/1/2019 8:00AM 23.6 2/1/2019 5:00PM 20.8 2/1/2019 9:00PM 13.4 I can get a plot with the x-axis as either date or ...
0
votes
1answer
129 views

Google Sheets Sumif formula error when criterion is date range

I am trying to sum data from the Jones tab if its between the dates in C1 & B10. But it returns an formula parse error. I assume I am using some syntax incorrectly, any suggestions? =sumifs(...
1
vote
2answers
69 views

Possible to add a third color to a 'bar' sparkline?

I have three columns of data that I want to represent with sparklines as a stacked bar chart. For instance, a row like 12 5 1 with this formula =SPARKLINE(C2:E2,{"charttype","bar&...
0
votes
1answer
20 views

How to include VLOOKUP with a loop for a list of data on Google Scripts?

I have a list of data on Sheet1 columns A-N, and a list of updates on Sheet2, columns A-N. I have a code that imports data to Sheet2, and I would like to add to my code a similar function to VLOOKUP. ...
0
votes
1answer
20 views

How do I search a string, and return all values?

How do I use the script editor to search a string in one cell, and return all results from another sheet? For Example: I have my string in cell B1 - which is the item number Data to be searched in a ...
0
votes
0answers
11 views

Can I create a Google Sheet macro to take the content from one cell and make it the hyperlink for another cell?

I do a report every month that has a column of Page titles and the next column is the URL. My boss wants the page titles to have the hyperlink applied to them so we can remove the hyperlink column. I'...
1
vote
1answer
12 views

Making dynamic number of arguments based on a specific criteria for importrange function

I am counting number of cases per day in each month by importrange data from daily sheets in every month with this formula on D3 for example: =SUM(IMPORTRANGE($Q$5,R5),IMPORTRANGE($Q$6,R5),IMPORTRANGE(...
-2
votes
0answers
9 views

How do i give a certain cell a number value in google sheets?

I am not sure how to word this, but i am making a spreadsheet and wanted to know how to give a number value of 600,000 to B2. So if i put 3 into B2 it would be 1.8 million and then using B7 i could ...
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, ...
1
vote
1answer
82 views

Google Sheets TIMEVALUE can't parse time string

=TIMEVALUE("14:15:30") returns a #VALUE! error "TIMEVALUE parameter '14:15:30' cannot be parsed to date/time". I searched the web for a bit, and according to some people the time string may use wrong ...
8
votes
2answers
13k views

How to convert all numbers regardless of decimal separator into valid number of current locale?

I have a column with text looking like this: 123,123 123.123 123,123 123.123 I would like another column to parse this into decimal numbers, disregarding the different decimal separators (dot . and ...
3
votes
2answers
7k views

How to show page number in “page X of Y” format?

I can select to show page number in Google Sheets print options. However, what I want is to have "Page X of Y", e.g. "Page 1 of 5". Is it at all possible to do in Google Sheets?
0
votes
2answers
1k views

Import range from 3 sheets/tabs into 4th

I currently have 3 sheets (tabs) in a Google Sheets workbook. Each column header (Row 1) in the 3 sheets are identical. I'd like to create a 4th sheet that combines ALL the contents in the 3 sheets ...
0
votes
1answer
140 views

How can I do an aggregate function with multiple columns with a Google Sheets query?

I've got two columns of 10 numbers in Google Sheets. I'd like to average the numbers in both columns using a Google Sheets query. Something like this: =query(A1:B11, "select avg(A&B)", 1) <- ...
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 >...

1
2 3 4 5
133