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
18 views

Google Sheets: Generate column in query based on reference range matches?

I'm no sheets expert, and I couldn't find the answer searching likely due to not using right search terms. Sorry if this is already answered and sorry if it's not possible. We have a data source that ...
0
votes
0answers
6 views

How do I get the selected cell on another sheet?

ok here is the manual situation in the spreadsheet: 2 sheets named Green and Blue Blue is the active sheet and B3 is selected. If I tab over to the Green sheet, D7 is selected. So I know somewhere ...
0
votes
1answer
21 views

Google Sheets Query - Group by more than one column without a pivot table

I have a timesheet dataset containing 4 columns: Column 1 - A workers name Column 2 - The date they worked Column 3 - The project they worked on Column 4 - The hours they worked on that date, on ...
0
votes
1answer
12 views

Continuation token: Generate file listing of a google folder into a google sheet

I have a folder on Google with 18k files and need to list some variables from them into Google sheet. A solution for this was found on Insert image from Google Drive into Google Sheets However, ...
0
votes
0answers
10 views

Finding a cell value based on row and column

I have a little problem that might not be solved just with formulas. And I am not so advanced in JavaScript yet. I have a list of products, our salesmen, customers and time when and how much the are ...
0
votes
0answers
5 views

How can I search multiple terms for “text contains” using a custom formula with the Google Sheets filter tool?

I want to use google sheet's filter tool to search multiple words under the "text contains" option. However, it only let's you search one term at a time. I have tried using different formulas under ...
0
votes
1answer
9 views

What's a google sheet formula for fractional part of the number

I need a formula to get only the fractional part of a given number. It should take any number and return its fractional part which is between 0 and 0.9999... For example 123.456 -> 0.456 0 -...
0
votes
0answers
8 views

Accessing info inside links in google sheets

Background: I have a Google Sheet that has some links in it. Of course, links don't always have the contents of what you are looking for the URL, so trying to search for it later is hard. Question: ...
0
votes
0answers
18 views

Is it possible to acheive this in google sheets?

I want to implement the following in google sheets, but I don't know how to or if it is even possible I have a sheet, let's call it "products", in which every entry has an ID tag and a value I want ...
0
votes
0answers
14 views

Google sheets split script?

I am trying to figure out a script that basically splits a cell horizontally in a google sheet that auto populates with a form. =SPLIT(AF5, ",") formula works, but I when a new form entry is added, ...
0
votes
0answers
11 views

Why isn't there an undline button in Google Sheets?

In word processing and spreadsheet applications, there's always the "big three" formatting options that have quick-access buttons: bold, italics, and underline. Why is underline missing in Google ...
0
votes
2answers
39 views

Extract the Day of the month (dd) from a date in Query()

I have a list of transactions for a month, all include a date (Column B - dd/mm/yyyy) and a £in figure (Column F). I want to create a table that shows the total taken on each day of the month so for ...
1
vote
1answer
37 views

speed up google sheet with large pivot table

I have several pivot tables that present 5-7 columns of data extracted from a sheet that has about 30 column and 5000 rows. That sheet in turn is updated everytime a line is entered in the orders ...
0
votes
0answers
13 views

Insert range using IF formula in SUMIF function with multiple criteria (using arrayformula)

I used an arrayformula with my sumif function which has several criteria and ranges to expand automatically across a set column (B). The formula below works perfectly: =ARRAYFORMULA(SUMIF('DATA'!$N:$...
1
vote
1answer
27 views

Is there a way to generate many rows and columns from a single value?

Let's say I have a sheet with a single column that lists all of my clients with each row being a unique value: e.g: client1,client2,client3,client4,... Goal: I'd like that in a separate sheet, 2 ...
0
votes
0answers
16 views

My conditional formatting 'equal to' not working

I am trying to show a particular entry is equal to or greater than another entry by having the condition turn the cell green. But nothing I am trying is working. All other formatting works but when I ...
1
vote
1answer
16 views

Sumif and filter using regexmatch with multiple criteria

I need to sum specific cells in 1 column (Q) based on if the other columns (R, S) have a certain word(s) within the cell. For example in the picture if a cell in R or S contains the word toy or green,...
-1
votes
0answers
15 views

I can't see “Show edit history” in a Google Sheets worksheet, does anyone know why?

It is a feature that was really helpful for me in a very similar worksheet that ia had last year but that is not available anymore. It is just not available in the list of options. Thanks!
0
votes
0answers
11 views

How to embed a Google Spreadsheet with just cells and a sheet list, but shows the navigation bar? [duplicate]

I'm using https://docs.google.com/spreadsheet/ccc?key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&rm=minimal#gid=53 I need to hide toolbar but shows the navigation bar.
0
votes
0answers
20 views

Fetching sheet name dynamically in google sheet

I am having 2 google sheet called 'Repository' and 'Order'. Repository file have many sheets like Note, Books, Stationery, Etc. Each item have a unique code. I have to fetch description and price of ...
0
votes
1answer
25 views

How to confirm if values are in a column and move to a new sheet - Google Script

In Google Sheets, I need to check column F if certain values are present. If the values I have specified are in column F, it will copy the entire row and paste it on a new sheet. The code works when ...
0
votes
0answers
15 views

How to calculate the difference between two times accurately across midnight? [duplicate]

I've seen this answer on a related question which is an excellent simple solution to calculating the difference between two times. However I've run into an issue when trying to calculate the ...
0
votes
0answers
12 views

How can I keep the parenthesis in this formula from moving? `=(-0.01 - 0.29)- (0 - 0)`

I have a cell where I would like the formula to be =(-0.01 - 0.29)- (0 - 0). The order of the parentheses is particularly important because of some regex commands down the line. However google ...
0
votes
1answer
35 views

How do I import data from one Google spreadsheet to another

I need to import a range of data from one Google spreadsheet to another but I keep getting the error: "Cannot find range or sheet for imported range using this formula" =IMPORTRANGE("https://docs....
0
votes
0answers
11 views

How can I get a date in Gsheet to repeat based on x?

I am trying to get repeatable schedules set up for users depending on how many people are in the pool. The pseudo-code would be If dev = x then repeat schedule after x weeks for y I've created a ...
0
votes
1answer
14 views

How to use external data range for a Google sheet pivot?

I'd like to use another spreadsheet's data as the data range for a pivot table. In order to import data from another spreadsheet, one can use the impotrange function. But can it be imported directly ...
0
votes
0answers
11 views

IFTTT trigger doesn't fire unless I click “Check now”

I've made an applet to add events from Google Calendar to Google Sheets. It doesn't fire when I create a new event (I've left it for hours,) it will only trigger when I click "Check now" on the applet ...
-1
votes
0answers
10 views

How do I write a script so that a button will recreate a range of cells

My document has the following cells: Item Name Size Impact Reason I would like to add a button which will create the same cells below this one, but for item number 2. So clicking this ...
1
vote
1answer
30 views

Google Sheets - Arrayformula with textjoin

I'm trying to figure out how to make my arrayformula with textjoin to work, but I'm kind of lost. What should be done is that F3 to F5 should have a formula attached, so it displays as showed. The ...
0
votes
1answer
11 views

Google Sheets calendar script is timing out

function onEdit(e) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); if(sheet.getName() != "Sheet2") { return; } var lastColoum = sheet....
0
votes
1answer
30 views

Function to copy fields from one sheet to another based on IF statements

I'm trying to figure out a way to copy specific fields to other sheets based on IF statements. I have 4 sheets, the Customer Subscription Sheet (Sheet 1), Month 1 (Sheet 2), Month 2 (Sheet 3) and ...
1
vote
1answer
48 views

Run formula that checks multiple cells and outputs to one cell

I want to run a formula that checks each cell between say F2 and F8, and if they match the relative cell in column B, it adds those cells together, and outputs in cell F9. Example expected result: |...
0
votes
1answer
24 views

Update two Google documents at the same time

I have a copy of a Google sheet (single sheet) that I want populated everytime someone edits the original. Is this possible?
0
votes
0answers
25 views

Format dates in Google spreadsheets script?

First of all,please excuse me for my bad English. I am splitting multi-line cell into different rows These are the scripts I use // Main of this script. function result(range) { var output = []; ...
0
votes
0answers
23 views

Updating a cell on another sheet by changing dashboard

I am currently working on a to-do list· dashboard for my household chores and have a question. I am using a filter to pull daily tasks out of a raw data table, and would like to be able to mark each ...
0
votes
1answer
28 views

I want to bring in already filtered data into a new spreadsheet

Is it possible to reference filtered data from one sheet to another in Google Sheets? I use the filter button to sort in a master sheet. I then use this formula in a new sheet: =IMPORTRANGE("...
0
votes
0answers
15 views

Spreadsheet formula to find all matching criteria then subject from cell in row

I have a Google sheets document arranged like so: Sheet 1: A | B | C tomatoes | 10 | **SUM** Sheet 2: A | B | C 2019-01-01T15:00:00 | tomatoes | 2 On Sheet 1, ...
0
votes
0answers
16 views

How will I remove duplicate texts from a single cell in Google Spreadsheet?

A1=NOSE ADJUSTMENT, BRAKE ADJUSTMENT, WIRING ADJUSTMENT, BRAKE ADJUSTMENT, I want: A1=NOSE ADJUSTMENT, BRAKE ADJUSTMENT, WIRING ADJUSTMENT How to remove BRAKE ADJUSTMENT from the cell?
-1
votes
0answers
14 views

I want auto change uppercase text and font size in google sheet script, anyone can help?

function onEdit(e) { if (typeof e.value != 'object') { e.range.setValue(e.value.toUpperCase()); } } function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss....
0
votes
0answers
8 views

Google Sheets - Preventing certain rows from being sorted

I'm trying to make a list of all my games and where they're from to try to kill somewhat of a backlog. The main problem I'm having is the sorting function since I don't know how to freeze multiple ...
0
votes
0answers
13 views

Justify a cell's text in Google Sheets

How do you justify text in Google Sheets? It's not available in the Horizontal align toolbar item or from Format > Align. This answer to an unrelated question mentions "justify text mode" but doesn'...
-1
votes
0answers
24 views

Synchronize multiple cells on Google Sheets

Building off of this question: Sync multiple cells so that if any one of them is changed, the others change as well Is there a way to synchronize multiple cells without individually entering them (e....
0
votes
0answers
6 views

CTRL F Search Hot Key not working

Ctrl F stopped working in Google Sheets. I believe someone disabled it. How do I re-enable this feature?
0
votes
1answer
16 views

How do I make the color of a cell change if the value has been modified in Google Sheet?

Basically, if the person enters "12" in his/her column, how can I make the cell change to a different color when the same person changes the value again? This would let other people know if there ...
0
votes
1answer
26 views

Pretty print google sheet formula?

Finding answers on Web Apps SE or Reddit is wonderful, but if you don't understand them you can't modify them. I recently came across the following as part of an answer: ={"Corrected List 2";...
0
votes
0answers
14 views

Why doesn't the GOOGLEFINANCE function work inside a calculated field in a pivot table?

I can use VLOOKUP to find something from a calculated field, but the GOOGLEFINANCE function just gets stuck loading forever: In this example, GOOGLEFINANCE is used to find the stock name, the "Tag" ...
0
votes
0answers
11 views

Is there a way I can filter a list of email addresses that contain words from another list in Google Sheets?

I have a list of thousands of email addresses and I'm trying to filter out ones that contain specific words. I have the list of words in the same spreadsheet and currently I am filtering the email ...
0
votes
0answers
17 views

Using same Google Spreadsheet with multiple people but different instance

I currently have 10 different Google Sheets that I manage. Multiple people use each sheet daily and I normally have to create new copies each time they need to use them. Is it possible to allow the ...
0
votes
1answer
28 views

More compact version of nested substitute function possible?

I want to make about a hundred changes in a column on a spreadsheet. These changes have to be remade each time a new copy of the original comes. The changes are spread over several thousand rows. ...
0
votes
0answers
9 views

Monthly Client retention/attrition calculation

I have a spreadsheet exported from our gym management software into google sheets and I am trying to figure out how I can can calculate our retention rate/attrition rate. As we allow month to month ...