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
6 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
7 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 ...
0
votes
0answers
4 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
195 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
13 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
19 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
7 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,...
0
votes
0answers
7 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 ...
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
11 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
62 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
127 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
64 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
8 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
9 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
6 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
80 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
0answers
10 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: currently I'm trying to group by more than 1 title, like this: However no matter what I do, I can only query ...
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 >...
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 ...
17
votes
6answers
73k views

How to create a checkbox column, that if selected will create a strike-through across the entire row in Google Sheets?

I have a Google Sheets. I want to set it up so that the first column is checkboxes, and if the checkbox is selected, the entire row gets a strike-through. How can I accomplish this? I followed this ...
0
votes
1answer
26 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
723 views

Check if value exists before copy-paste

Can someone help please as I am new to Google Scripts. I have this simple code that copies values from one sheet to another function copyrevision() { var ss = SpreadsheetApp.getActiveSpreadsheet ()...
1
vote
1answer
19 views

How to use a query to find a row of same name but last entry, then copy a value next to it (update request)

(This is an updated version of this question - I am trying my best to make myself clearer this time.) I am trying to use a query to copy values from one column to another. Both source and destination ...
1
vote
1answer
15 views

Fill in missing dates in Google Sheet query

I have a Google Sheet query like this: =QUERY(A2:I;"select I, sum(C) * -1, count(C) group by I order by I ASC"; 1) And it yield a result like this: In the source data not all months are ...
1
vote
1answer
13 views

Google Sheets value on chart off from source by very small fraction

Trying to figure out why Google Sheets chart is off from the source data by a small fraction. My sheet has 7 rows of data and a sum row. All data only has 2 decimal points and the sum obviously has 2 ...
1
vote
1answer
202 views

Populating alternatives in question 2 based on answers in question 1

I have ten people who sell something to companies, and I want to track what they do IN GOOGLE FORMS. Every agent has his own 10 companies that he contacts. So, in Question 1, I will have a drop-down ...
1
vote
1answer
34 views

Get historical stock price in another currency

This formula returns the daily stock prices of A1 in the last two years: =GOOGLEFINANCE(A1,"Price",today()-(365*2),today(),"DAILY") This formula returns the daily conversion rate ...
1
vote
1answer
16 views

FILTER between columns

I have thousands of data in 2 columns First one is ordered by export and the other by latest activity. The export columns has more queries than the latest activity. (can't change the export to the ...
0
votes
1answer
12 views

How to extract desired text from a cell

As you can see below I have these data in single cell I want only car nos as my result
2
votes
1answer
57 views

How to use a query to find a row of same name but last entry, then copy a value next to it

I am trying to use a query to copy values from one column to another. Both source and destination columns have a name on their left columns, and the name must match. The source names could be ...
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 ...
6
votes
4answers
16k views

How to reverse the y-axis in a Google Sheets graph?

I have data that represents the depth of an open mine in a transversal view. Currently, it looks like this: But since it represents the depth of a mine, I'd rather have it "flipped". So the y-axis, ...
1
vote
1answer
62 views

How to prevent cut-and-paste data from changing collapsed grouped cells?

I'm completely lost and don't know how to paste any data without affecting those collapsed rows. For example, I have such a list: And I want to paste numbers from 1 to 4 for every capital letter ...
0
votes
2answers
33 views

Move data script

I am setting up a script to copy data from one location and paste values into another location (same tab). However it is only picking up the first line. How can i get it to pick up the whole range ? ...
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?
2
votes
1answer
36 views

Replace first value in a given range (column)

How would I look up the first value < 0 in this range and replace it with 0? If the value <0 does not exist, I'd like the column to remain intact. I've been struggling with this for a day now ...
0
votes
1answer
32 views

Google Spreadsheet Script Trigger: This app is blocked

I am trying to create a trigger for a script in google spreadsheet and upon trying to save the trigger I am getting the following error: This app is blocked This app tried to access sensitive info in ...
0
votes
1answer
29 views

How can I create a filter on Google sheets to filter only words that begin with uppercase?

I have a column with 2 kind of words: words without uppercase (betrachtet) and words that begin with uppercase (Analysieren) I need a filter that shows my only those words which begin with uppercase....
1
vote
0answers
17 views

Need help with google sheets, sending info from one sheet to another, but allowing the second sheet to be modifiable!

Guys for example I have this master sheet, which has one specific column that I want to filter by. for example it has different job titles, and I want that to be on another sheet so I can create "...

1
2 3 4 5
133