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.
0
votes
1answer
4 views
How to select existing sheet to be populated by a Google form?
I have a team working in pan India with 6 Team leaders. I have created a Google form to collect the everyday status report.
Now the question is:
How can I assign a particular sheet[already created in ...
0
votes
1answer
6 views
How to use a cell value to define the sheet on IMPORTRANGE
Trying to create a spreadsheet that reads values from a certain sheet of another file using importrange formula.
I am trying that the referenced external cell be a cell on the current one so I could ...
0
votes
0answers
6 views
Copy data generated by QUERY
I want to copy the values generated by a complex scripted query to another sheet, for users to work on without deleting the original query.
The query generates a variable range of data, depending on ...
0
votes
1answer
7 views
How to import historical open price in Google Sheets with GOOGLEFINANCE?
For some reason, I can't get the google finance function to work with any open-price lookups I do.
For example:
=GOOGLEFINANCE("AAPL","priceopen", DATE(2019,4,12))
comes back as #N/A with message:
...
0
votes
1answer
13 views
Simple Scorecard Conditional Formatting
I simply want to create a scoresheet comparing scores to par. If less than par then a red number, if more than par then highlight red, if equal to par then highlight green. Here is what I'm trying ...
0
votes
1answer
15 views
Using a cell value, to dynamically create a formula, that links to a cell in a different sheet (tab)
Ok. This is a bit complicated, but I will do my best to describe the problem. All of these elements are located in multiple Sheet pages (Tabs) within one singular Google Sheet (Workbook).
Sheet 1: ...
1
vote
2answers
29 views
How to keep a checkbox value to reuse it later in Google Sheets
I'm working on a Google sheet actually (only for fun) and I'm actually stuck on something, as I said in the title, I want to keep a checkbox value in Google sheet.
I will explain my problem.
I'm ...
0
votes
1answer
19 views
Grabbing only non-empty cells from a row range for every row
I have a survey that sends responses into a Google sheet. The raw data that is inserted is messy, so I have another sheet that processes the incoming raw data.
One of the things I need to do is: from ...
0
votes
0answers
7 views
Google Sheets text wrap no longer expands cell
All of a sudden I seem to be having trouble in my Google Sheets auto extending height of cell with text wrap, for certain cells.
Mac OS: 10.13.6 (17G65)
Chrome: Version 73.0.3683.103 (Official Build)...
1
vote
1answer
12 views
Compile cells when cell contains certain value
In Google Sheets, I have a range of values as follows:
I am looking for a formula that will search the range, find all of the cells that is a 'wire' (contains the term "wire") and outputs the range ...
0
votes
2answers
13 views
Format cell if all the cells in a row are not empty
I tried all kind of formula but they all seem to work the other way around for me. I have a very large sheet with lots of columns. I'd like to have the first cell in the row to be a certain color if ...
0
votes
0answers
13 views
Email Template for Mail merge not working!
function getRowsData(sheet, range, columnHeadersRowIndex) {
columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
var numColumns = range.getEndColumn() - range.getColumn() + 1;
...
0
votes
1answer
9 views
Can I navigate from a pivot table back to the original database?
Once I am "in" a pivot sheet, is there an easy way to click on a row or cell in the Pivot table and navigate directly back to the related data in the original database (Google Sheet), from which the ...
0
votes
0answers
12 views
How do I circumvent protected sheets to expand or collapse groups and rows in Google Sheets?
A budget I am working on has each month on the same sheet and I would like to control which months are visible when comparing expenditure and income. In order to do so, I grouped the rows and columns ...
1
vote
1answer
20 views
How to convert multi-value answers into single column in Google Forms' response sheet?
I have a Google form that asks for Name and up to 3 Score. The data is dumped into a sheet like this
But that data is not very convenient. I want to have the data like this...
Can I do that by ...
1
vote
1answer
11 views
Finding multiple “most common values” in a column
I am trying to display the 5 most commonly appearing text values in a column in Google Sheets.
The following will give me the most common value in column A:
=ArrayFormula(INDEX(A:A,MODE(MATCH(A:A,A:...
0
votes
0answers
11 views
Trying to make a schedule [on hold]
I am trying to make a schedule and assign priority on each date that a certain district is covered. I have 12 people working on a specific date. I want to make sure district 10,20,30,40,50,60,70, and ...
0
votes
0answers
21 views
Add another cell A2 to the email Template. What code changes required?
I am currently using the following code to send mails from google sheets. I wanted to know what changes would be required to add another cell 'A2' to the template as I have to add an image at the end ...
0
votes
1answer
9 views
Copy Google form and linked sheet preserving existing formulas and formatting?
I am required to copy an existing Google Form, however, the linked results sheet has deep formatting and formula.
Apparently, the new form requires a new sheet to be linked to form results. How can ...
0
votes
1answer
17 views
How to reference a cell while keeping its color formatting?
Is it possible to reference a cell which contains "ABC" where B is blue and C is yellow while keeping the color formatting?
Assume A1 contains the colored text, then I want B1 to refer to A1 and ...
1
vote
1answer
11 views
Formating a number with specific spacing
I'm attempting to pull a particular option value from Yahoo! into Google Sheets. The base URL is: https://finance.yahoo.com/quote/GOOG190418C00800000
When breaking the formula up, I have to ...
0
votes
1answer
9 views
Description based on each checkbox “checked” to populate into a summary on the 2nd tab (sheet)
I am building a spreadsheet that has to tab (sheets), the 1st I have an "assessment checklist" with about 250-30 checkboxes based on what is found. Example (Checkbox is in "A" column and description ...
2
votes
1answer
10 views
Format cell, If word from list in cell
I'm trying to color tag cells according to keywords they may contain. I've searched just about everywhere, and have tried various applications of the Search and Find functions to no avail.
Imagine I ...
0
votes
2answers
8 views
Download 1 sheet from Google Sheets
I've got a spreadsheet (workbook) in Google Drive Sheets that has multiple sheets (tabs/worksheets).
I want to export just one worksheet from the workbook as an XLS file.
Is this possible?
I ...
1
vote
1answer
13 views
Disable Conditional Formatting without deleting the rules
I have multiple conditional formatting rules in one of my Google Sheets, I would like to disable a few of those rules for some time and would like to enable them again. But I don't see any such ...
0
votes
1answer
18 views
How to navigate to a certain sheet in Google Sheets?
In my previous related question, I asked how to navigate to a certain column, however, now I also want to navigate to a certain sheet.
So I need a link from a cell, and by clicking it, I want to jump ...
0
votes
0answers
12 views
PLease check my delete Function
I modified some google apps script but cant get right the delete (Hapus) function...please see the code below
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/...
1
vote
2answers
19 views
How can I create a recursive formula in Google Sheets?
I'm making a task tracker in Google Sheets and am trying to implement subtasks.
I am trying to create a formula that checks a row's "Parent Task" column, and if there is a parent task, indents the ...
0
votes
0answers
6 views
How can I paste an email template with a logo into one cell of a google sheet? [closed]
How can I paste an email template with a logo into one cell of a google sheet?
The Email template is being used with a mail merge code on google sheets. But I can't seem to put the logo in!
Would I ...
0
votes
2answers
14 views
How Do I Quickly Merge A Large Amount Of Cells?
So I'm trying to merge all cells in Col A and B so that A1 and B1 are one cell all the way for 150 rows, anybody knows how?
0
votes
1answer
25 views
I want to get rid of google finance disclaimer
I want to get rid of Google finance disclaimer.
I used the =GOOGLEFINANCE formula in a sheet. It triggered the display of a disclaimer at the bottom of the sheet that says:
Quotes are not sourced ...
1
vote
1answer
18 views
Total Wages Calculations
https://docs.google.com/spreadsheets/d/13HdNN_qa9rhLFyMe0N9I9EusmBudi_dcdBkc6UeLHMM/edit#gid=878850570
I am trying to total Normal Pay but the formula does not Multiply Regular Hours by Normal Rate ...
0
votes
1answer
23 views
Find previous non-blank cell, but without a specified range in Google Sheets
I've come across plenty of formulas to find blank cells within a certain specified range, but can't seem to figure this out / find an answer.
I have a spreadsheet kind of like this:
.
Where column ...
0
votes
1answer
10 views
How can I conditionally format cells to denote past/future/current dates?
I have a church rota Google Sheet. Column A shows the date of each service.
To make it more obvious at a glance, I would like cells in column A to apply formatting so that dates in the past are ...
1
vote
1answer
18 views
Problem inserting a column and cell reference/link
I have a sort of summary in my Google sheet.
In the second column, I refer to the sub tables further in the sheet with e.g. this content:
https://docs.google.com/spreadsheets/d/1J-
...
0
votes
0answers
23 views
Formatting script applicable to a range, not an entire sheet
I have the following script, which applies formatting in row 1 to an entire spreadsheet. How can I limit the formatting application to a range (ex. A7:AN135), so that the header rows are excluded from ...
0
votes
1answer
26 views
Google Spreadsheets timestamp
I'm trying to have a timestamp automatically appear in column L (12) in a Google Spreadsheet if a tick box is ticked on the same row (in column A). The app script code that I modified from this source ...
0
votes
1answer
10 views
Display name of sheet with highest value in cell
I have a sheet for each data set i have. I want to display in a cell the name of the sheet with the highest value in all the B2 cells.
0
votes
1answer
7 views
How do I insert different cell data from a table into a hyperlink?
I have a table of different cell data that I want to insert into different hyperlinks for each item:
=HYPERLINK("https://www.otcmarkets.com/stock/CELL DATA TO BE INSERTED/security","SECURITY")
Cell ...
0
votes
0answers
15 views
how can i find and append a cell with script editor in google sheets
I have a script that will find the value in a lookup from column b and column a what I am trying to do is find the row and append the data in column n of that row
is there a way of doing this, the ...
0
votes
1answer
17 views
How do you make the graph relative in Google Spreadsheet?
I have a simple graph in Google Spreadsheet just showing weights from different dates. The problem I have is that the graph is starting from 0, giving it a lot of dead space. I want it to be relative ...
0
votes
1answer
7 views
How to ignore certain strings when creating unique list of words in Google Sheets?
In Google Sheets, I am using the formula to create a unique list of words:
=unique(transpose(split(ArrayFormula(concatenate(B2:E"+")),"+")))
But I want to filter out certain strings.
My unique list ...
1
vote
1answer
7 views
Automatic cell coloring via conditional formatting
I am working on a sheet for a game I play and having some trouble with cell coloring.
I have one sheet, named "PlayerList" which maintains a list of player names, class and role. Players are ...
0
votes
2answers
7 views
How to create a unique list from a selected range of cells
I have a list in cells A1:A16 of foods and want to create a unique list with each item only appearing once in the list:
Eggs
Bacon
Hashbrowns
Baked Beans
Avocado
Coffee
Bread
Bread
Sandwhich meet
...
0
votes
1answer
18 views
Method to preserve cell reference in a formula upon adding rows in reference sheet?
I have a Google Sheets with two(2) tabs. The first sheet is just a running tally of raw data and is updated with new rows at the top weekly.
In tab 2 there is just a single query(arrayformula)... ...
1
vote
1answer
8 views
Google Sheets referencing a cell adjacent to a cell on another sheet
I've got a calendar in google sheets with each cell referencing a cell value pulled from weekly sheets. I'd like to be able to enter the cell reference for the first of the month, then have the rest ...
0
votes
0answers
14 views
Order query() by dates using the format Mon/D/YY (Apr/13/19)
I'm importing data from a sheet that uses the Mon/D/YY date format (Apr/13/19).
When I try to sort the entries chronologically with the following request, they're instead sorted alphabetically (A is ...
0
votes
0answers
17 views
Import range function in Google sheet
I am facing a difficulty when I use import range function in Google sheet to combine all data in a Master file from separated many different Google sheets.
It works well while using import range ...
-1
votes
1answer
21 views
Google Sheets Two-Way Databinding?
I want to make a spreadsheet in Google Sheets with a user friendly editable front end and a back end with an arbitrary number of rows containing the data to be displayed. There should be a dropdown on ...
1
vote
1answer
16 views
Selecting row with best value for items with several versions/upgrades
I'm sure my problem is quite simple to solve, but I've been on it for a while and I could use some help.
I have a huge table, but the problem boils down to this: some items have 2 versions (one ...
