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
0answers
3 views
Copy template to a new sheet referencing a cell number that is to increase incrementally
I have a template sheet that I would like to be able be able to copy with a unique name.
Needing a single function to execute these steps:
The document containing TEMPLATE is opened
Button is ...
0
votes
0answers
4 views
Fractional formatting and math
Looked over the possibilities for help and coming up empty.
I have written a spreadsheet which does some routine calcs for me. The problem comes in the formatting of certain cells.
What I have:
A ...
0
votes
2answers
15 views
How to get price of specific cryptocurrencies at specific times?
I have an assortment of cryptocurrency transactions over time. Various currency pairs at various prices. I need to figure out what the price of those currencies was, in Bitcoin, at the time that I ...
0
votes
1answer
10 views
How to return all values (horizontal array) in B that has the same key stored in A?
Sample Sheet 1:
A B
1 a x
2 a y
3 a z
4 b i
5 b h
6 b w
Formula result, in Sheet 2, I am looking for:
A B C D
1 a x y z
2 b i h w
0
votes
0answers
15 views
Google script to convert function result to an array
I have this bit of code that that returns a list of all not hidden rows on my Google Sheet, ie it returns only visible rows, I injected an F and incremented the number by one so I can get it in ...
0
votes
1answer
16 views
Getting data from another Google sheet that isn't in the same order
I have a very basic knowledge of deeper functions of Google sheets and solving this has been a little bit of pain in the butt so far.
I have a spreadsheet A
column A contains ID
column I that has ...
0
votes
1answer
28 views
Protect spreadsheet against duplicate data and sheet addition/deletion
There are some people that love to troll and spam my Google spreadsheet with useless info. Google Sheet lacks granular permission restriction.
I need to prevent duplicate data from being submitted. I ...
0
votes
2answers
13 views
How to apply conditional formatting only if cell has X value? [google sheet]
I have a sheet with statistical data to which I had applied color scale conditional formatting. Some rows I set to have green for highest and red for lowest number in row and other rows the other way ...
2
votes
2answers
36 views
Query returning #VALUE error when the column has no values
The below query returns #VALUE error when the column H is completely empty:
=QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&...
2
votes
1answer
13 views
Return first vertical value that matches given criterion
On Google Sheets, I would like to figure out a way to have a cell return the first value in column C where the same row in column D matches the criterion ("Blue" for example, as opposed to "Red"). I ...
1
vote
1answer
21 views
Creating running total chart
I want to create a chart in Google Sheets with cumulative Profit (H) column but without creating the second column (I). Are there any solutions?
0
votes
0answers
7 views
Hot to switch master data in google sheet
I have a spreadsheet with an overview of calculating some income based taxes
now all tax percentages are fixed and for a specific year
I want to move to have a list of master data: the percentages per ...
2
votes
1answer
22 views
Enable grouping and ungrouping of rows in a locked worksheet (Google Sheets)
I need to share a spreadsheet with some locked cells, but it is necessary that the user can use the grouping and ungrouping functions of rows. Within the pooled range there are locked and unlocked ...
1
vote
2answers
20 views
Conditional formatting rule that compares against a range of values
I have a list of 2019 dates (col A) and holiday dates (col G). Wherever there's a date in col A that matches any date in the range G2:G20, I would like to highlight it in grey. See this sample ...
0
votes
2answers
14 views
How to scan rows for match with ArrayFormula?
Trying to search a row for a given value, but want to make an ARRAYFORMULA so that every time a new row is added the search will be performed on that row.
Currently using =IF(COUNTIF($A2:$EI2,"="&...
1
vote
1answer
26 views
How to multiply a query column by a cell refference on Google Sheets?
Below is the query that I use, and it works:
=QUERY(Input!A2:I33;
"SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&TEXT(Input!L8;"dd/mm/yyyy")&"'...
1
vote
1answer
15 views
How to return unique list of text pivot by Query
I'm trying to mimic a pivot table behaviour where you would grab all distinct values from Col B and, by each of those unique Col B values, you associate all each respective values, that must also be ...
1
vote
1answer
25 views
JOIN two columns in one cell
What should I do if I want every time I fill out column C, to all data from B6 to C14 would appear on the notes?
0
votes
1answer
9 views
Spell check with formula or apps script
I have text in the table. I want to check the text whether there is a typo or not. Usually to check my typo using the default Spelling Tool from Google Sheet.
The question is: Can the spelling check ...
0
votes
0answers
9 views
Updating a total and calculating from the new total
I have a Google sheet that I'm sharing with my students that is being used for a stock market game we are playing. There is an opening price for the shares and a column for each group. I want the ...
0
votes
2answers
17 views
How to quickly re-arrange sheet order in Google Sheets?
If I have a Google Sheets document (Workbook in Excel terms) with 20 sheets (Worksheet in Excel terms) and I want to rearrange the order of the sheets currently the only way I know to do this is to ...
0
votes
1answer
9 views
How to apply conditional data validation when a cell with a defined value is changed?
I have a spreadsheet that's basically a grid checklist, let's say 10 columns and 20 rows.
There are multiple cells that have N/A as the value, and I'm worried going through the checklist that one ...
0
votes
0answers
6 views
How do I send changes done in a Google Spreadsheet document turn into a HighRise notification? [on hold]
Is there a way to set this up?
I BCC whenever I contact customers and the replies show up in HighRise through the dropbox email.
But can I make changes done, or a daily summary of changes made to a ...
1
vote
1answer
12 views
Query Formula excludes results with new line character
It seems there may be a bug in Google Sheets' Query formula where results with a new line character (char 10) are excluded when the column is filtered. Is there any way around this or am I doing ...
0
votes
2answers
11 views
FILTER for multiple conditions including OR
How can I use FILTER in a single formula to check for multiple conditions, such as in this case?
The filtered range must be equals1 AND notequal2 AND equals3 OR equals4
This is of course an ...
0
votes
0answers
12 views
How to record Google Form responses back to Google Sheets
I am a beginner in Google scripts.
I have learned how to write to a Google scripts sheet using the examples below. However, I do not know how to set the range ("?") Because the range ("AB7") will ...
1
vote
1answer
12 views
Combing two ranges with identical column structure from two different sheets (in same spreadsheet)
I'm doing a simple ticketing system, where I have two sheets, "Form Responses" and "Email Responses" both of which are populated automatically as their names imply (one from a form, one from an email ...
0
votes
3answers
38 views
Google scripts: Contacts to Spreadsheet Issue. Names with Address, Phone Number, Email, and Job Titles
I have over 500 contacts in Google. I am wanting to list all the contacts that are associated with a particular company into a sheet. When I run the following script, I receive a timeout error.
...
1
vote
1answer
19 views
Import Bitcoin price in EUR using the IMPORTXML fucntion
Hello does anybody know how to import the Bitcoin price in euro into a Google Sheets document by using the IMPORTXML function?
I am as far as I get the Bitcoin USD price -
=IMPORTXML("https://...
0
votes
1answer
14 views
IMPORTXML reading decimal separator as thousands separator
When I import this item via IMPORTXML and the spreadsheet locale is US (or other country that uses dot as decimal separator) it works fine:
=IMPORTXML("http://bvmf.bmfbovespa.com.br/cias-listadas/...
-1
votes
0answers
9 views
Variable table length in Google Docs/Sheets mail merge
Suppose I have data in a Google Spreadsheet with the column names Name and Amount. I would like to do a mail merge in Google Docs with output as pages in a Google Doc (or PDF) wherein each page ...
1
vote
1answer
9 views
How to combine dynamic columns in Google Sheets while retaining data in other rows?
I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns.
I want to ...
0
votes
0answers
22 views
Upload from URL (on Google Sheet) to directly in Google Drive
I have a google sheet on which links are generating (by calculation) for downloading / uploading in G-Drive.
I want when links are generated, I can upload file (from generated link) to directly in my ...
0
votes
0answers
15 views
Automatically highlight changes in a sheet
I am having a Master engg roster sheet for manpower scheduling and another engg sheet that is shared with everyone.
Master engg roster ( not shared with people)
engg roster ( shared with people)
I ...
0
votes
0answers
12 views
Retrieve specific value from pivot table
I have several sheets with a pivot table in each sheet.
Each pivot table has the same structure of data, with 2 columns and 5 rows:
5 labels with a numeric value and the percentage of the total ...
1
vote
1answer
13 views
Shortcut to put focus at end of value in cell
Is there a way to do this without using a mouse?
I'm in Google Sheets, with the focus on a specific cell, which already has content in it. I want the focus to move to the very end of the value in the ...
1
vote
1answer
14 views
How to color cells in column A if cells in other columns relating to those column A cells are filled in?
Column A contains student names. The other columns show work that is missing or incomplete.
I'd like to be able to have cells in column A colored in if work from various classes is missing, without ...
1
vote
1answer
18 views
Creating a parent ID cell in Google Sheets
I've got a spreadsheet of various categories and I'd like to create a column which gets the parent name of that particular category.
They are currently broken down as follows:
01.01 Category 1
01.02 ...
1
vote
2answers
16 views
Imported values in Google Sheets from Google Forms
Using Google Forms for a quiz...
The quiz is graded /30.
Value is transmitted into Google Sheets.
Cell C2 displays a value of grade... here, 14/30 (as an example)
Value for cell C2 reads as 14
...
1
vote
2answers
20 views
What function can I use to conditionally format text depending on cursor position or active cell?
In Google Sheets, I have a 'Freeze' on Column A.
I want the text in Column A, Row X to Conditionally Format as BOLD RED, while my cursor is on any cell for Row X.
Is there a way to manage that?
1
vote
1answer
36 views
Skip empty rows while copying
I have this script in Google Sheets which is able to archive range Sheet1!A5:H to Sheet2
function tc() {
var reference = 'sheet1!A2';
var rng = SpreadsheetApp.getActiveSpreadsheet().getRange(...
2
votes
2answers
52 views
Automatic currency conversion in Google Sheets
I have a personal accounting sheet that is mainly accounting in Euros, but for every manual Euro (EUR) entry I do, I want to have that entry converted to USD in the previous column next to it ...
0
votes
1answer
33 views
How to get value from one column App Script? [closed]
I am learning the Google app script.
I want to get data from one column (B) but the range is not specific it depends on the data.
I have this script but I do not know or understand how to get the ...
0
votes
1answer
12 views
Comparison Column Chart
I'm trying to make a chart in Google Sheets to compare how much two people have paid towards something. What I'm looking for is the chart to have several cost categories (groceries, dining out, pets, ...
0
votes
1answer
12 views
Retrieve fund price from Vanguard website using importxml in Google Sheets
This was working in Google Sheets but now I get #N/A message:
Imported Content is Empty
A1: https://institutional.vanguard.com/web/c1/product-details/fund/1680
A2: //*[@id='dailyNavValue']
A3: ...
-1
votes
1answer
7 views
REGEXMATCH doesn't match, but should work
Why the following REGEXMATCH results FALSE?
=REGEXMATCH("aaa E: 111 111 bbb"; " E: ([\d\s]+) bbb")
How should I fix it?
0
votes
1answer
25 views
QUERY error NO_COLUMN:col1
=query(importrange("12HAbnTv4g_6m1UeCOs9ZO0gAYvy8tlaXr-cc2ecPEic","Master!A:ba"),
"select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col53
where col53<> ...
1
vote
1answer
20 views
Pie chart with sub-slices per category in Google Sheets?
I have a sheet that contains a list of books, with metadata about those books. Two columns that exist are the library within which the book is stored, and the condition of the book. I'd like to create ...
0
votes
1answer
16 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
18 views
Google sheets, scatter plot, multiple datasets, with datapoint labels
I want to create a scatterplot in Google sheets with multiple datasets, and yet still be able to add data labels for each data point. Additionally, it's two independent axis, which means some X values ...
