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.
7,597
questions
0
votes
0answers
2 views
Google Sheets UNIQUE not removing all duplicates
I have a table in which the headers should be all the unique start and end times that are in another table. However, when I use the UNIQUE function, the result still contains duplicates. Does anyone ...
0
votes
0answers
6 views
Colour Coding for Null Values in Choropleth in Dash components
I am having issues while color coding for choropleth in dash components.
For null values the color needs to be grey, but I am getting absolutely no color and the coordinates also disappear.
Would ...
0
votes
0answers
4 views
Flexible formula to match strings in 1 row to run a formula in matching columns in another row
I have a row where all strings have the identical format (XX-#####-##) in which Xs are 2 upper case alphabet letters and the #s are integers.
Short example, see the doc for more
KC-30000-00 | KC-30000-...
0
votes
0answers
9 views
Bad with math. Need to find how 2 sets of data interact
I have 2 data sets. Wet Density and Density Count. When WD goes up then DC goes down and vice versa. Is it possible to set-up a library of data of known WD and DC numbers, work out the relationship ...
0
votes
0answers
7 views
How do I create a pivot table group in Google Sheets when the ungrouped values produce too many columns? Right click on the columns is not available
I have a Google spreadsheet for expenses: one column for the YYYY-MM-DD date of the expense, one column for the category, and one column for the amount. This sheet has thousands of rows. The goal is a ...
0
votes
0answers
12 views
What formula do I need to use to determine the % of events someone has attended since their initial date attending?
I have a spreadsheet with 2 columns; A has names and B has the date they attended the event. I created a pivot table with the data; the rows have the names and the columns have various date data.
...
0
votes
0answers
16 views
Google Sheet files come out in Gibberish
I keep a lot of financial and tax-related information in Google sheets. Now that the year is over, I've called up some of them and all I get is rows and rows of letters, boxes, diamonds, etc. ...
0
votes
0answers
8 views
automatic task status
I have a column with a deadline in each row and I would like create new column of task status (In Progress and Completed) Example: In row 2 the deadline is "20/02/2022" and in other column ...
-1
votes
0answers
13 views
Google finance formula for two year return for any nifty 50 stock
I tried to find two year return for tcs (a nifty stock) with some formulas, but still unable to get the results. Please help
0
votes
1answer
12 views
How do I get importrange and jointext to combine 3 name cells into 1 cell on a new worksheet in Google sheets?
On Wksht A I have cells C5=last name (i.e. Doe), D5=first name (i.e. Jane), E5=first name (i.e. John). This is to cover 2 joint owners (i.e. husband and wife) in our housing complex. I want to import ...
0
votes
0answers
19 views
Automatically apply a formula and a when a new row gets added via a form API
Hi I have a wordpress form, linked to a google sheet that transfers over entries as they come in, now I have some formulas applied, but I don't want to have to add the formula to each new row that ...
0
votes
3answers
40 views
Google sheets convert time to decimal
I've got an issue with some time data that is exported from a program like this into cells (each number with text in each cell descending all in column a)
0h
14h
17h
17h
25h 30m
0h
8h 30m
7h
I need to ...
0
votes
1answer
20 views
Averaging win/loss data by day in Google Sheets
There's been similar questions answered here but I've been unable to apply those tips to my scenario. I keep data on gambling wins and loses. I want to calculate averages based on the day of the week. ...
0
votes
1answer
14 views
Format cell If date is in the current week [closed]
I have a few dates in Column "A" and I want to highlight cells that have dates from the current week. How to do it in Excel Or Google Sheets?
0
votes
1answer
17 views
Google Sheets' IMPORTDATA randomly converts number
We have a simple REST endpoint that returns a number as a string. The number is rounded to two decimals and has a point, like this: 2385.1
Google Sheets then sometimes transforms this into 177418 or ...
0
votes
1answer
29 views
I need a formula to sort a list in a regular alternating pattern according to numeric values assigned to each item
I’m working in Google sheets and need a formula to transform the information in columns A and B into a single column with the pattern shown in column C. The idea in Column C is to get as equal a ...
0
votes
0answers
13 views
How to make a macro in Google Sheets that runs when a cell changes value and then formats a range based on the value in the cell
I have some formulas that pulls out data for different graphs in Google Sheets.
These data can either be in full numbers (e.g. Revenue) or in %.
I have a dropdown for each graph where the user can ...
0
votes
0answers
16 views
Is it possible to create a presentation based on Google Sheets?
Let's say I have columns Name and Reference in a Google Sheet with multiple pages. I want to create a presentation (or something similar) that would do the following, automatically, based on a Google ...
0
votes
2answers
36 views
Multivalue checkbox in Google sheets
I want to have a checkbox that have three states (instead of True and False, it will have, let’s say, 1,2,3). Visually, it can be ticked, unticked or partially ticked, or different colors set by me.
...
0
votes
1answer
54 views
Compare and remove duplicates in Arrays (Google sheets)
I have two arrays of many rows , in my Google sheets, which have numbers such as
Active
Recovered
904;1505;1706
406;504;1502;1503
202;401;602;1104;1501
202;1502;1602;1704
I have shown only two ...
0
votes
0answers
13 views
Links to other Google Sheets
This may be question of understanding, rather than obtaining a solution. I have a Google Spreadsheet sheet embedded in a (public) word press website page. It contains columns with external hyperlinks ...
0
votes
1answer
35 views
Improve script performance
I wanted to ask, if I can do anymore improvements regarding my runtime, currently it takes roughly 100s to finish my script(since adding the formatting script below). Before adding additional ...
0
votes
1answer
13 views
Google Forms corresponding to a pre-set date on Sheets instead of being added consecutively
Is it possible to have Google Form data inputs being added not one after the other but corresponding with the Date that is pre-filled on the response form?
Here you can see how the data is currently ...
0
votes
1answer
17 views
"#NUM!" error in Google Sheets Script
When trying to Run the following script the result is always a #NUM! error - (Result is not a number) in cell "O5"
Both the cells are in currency format and I've tried changing them to ...
0
votes
1answer
22 views
Is there a way to add up the cost of multiple different items but not all the items in Googlesheets? [closed]
Im very new to this so please dont mind if its stupid
Im making a spreadsheet to manage your army in a game and I'm trying to add up the cost of different kind of divisions (Infantry, Sniper, Tanks, ...
1
vote
1answer
37 views
Google Script Substract Function
I want to create a script function that I will use with daily triggers:
function will subtract "Daily Fees" (a cell value that changes daily from "Open Trades" sheet) / from "...
0
votes
1answer
19 views
Calculate specific contents of a cell that contains a string of numbers and text
I have a sheet that contains cells that have lists of data within them, for example:
One cell contains: 1xExample, 2xAnotherExample, 5xThirdExample
The cell below contains: 1xAnotherExample, ...
0
votes
1answer
11 views
Convert Historical Currency in Multiple Rows with Preset Date and Amount
I downloaded some data from PayPal and they report certain transactions in the destination currency (PHP), but I need to get the currency I sent it in (USD), however, because these are historical ...
0
votes
0answers
21 views
Google Spreadsheets: Display static text on chart
I have a line chart that I've created in Google Sheets. This line chart is published to the web and embedded in another website as an iframe.
I have a cell in the underlying data that updates with a ...
-1
votes
0answers
17 views
Help formatting data on sheets from Google Forms
I am trying to format the sheet so that all the "items and quantities" list vertically for each time stamp. I have manually edited how I want it to look under the raw data:
https://docs....
0
votes
1answer
41 views
Using IMPORTXML in Google Sheets: How to Combine XML Data Sources as a Single Set Before Applying XPATH Query
Using the IMPORTXML function on Google Sheets, I've got two XML data sets at two URLs, respectively in cells A1 and A2. I've got the XPATH query in cell H1. I would like to run a single query on the ...
0
votes
0answers
12 views
Reorganize csv data
I work in a school that has multiple data entry forms and formats and I am trying to find a way to automate one example of data duplication.
We put student report card grades in on one online system, ...
0
votes
1answer
14 views
Query function with variable in the Col1=
Here' the formula:
=query({{$P$3:$P$34;$R$3:$R$34;$T$3:$T$34;$V$3:$V$34;$X$3:$X$34;$Z$3:$Z$34;$AB$3:$AB$34;$AD$3:$AD$34},{$Q$3:$Q$34;$S$3:$S$34;$U$3:$U$34;$W$3:$W$34;$Y$3:$Y$34;$AA$3:$AA$34;$AC$3:$AC$...
0
votes
0answers
19 views
Google Sheets - SUMIF Contains
EDIT2: I resolved it by using the following formula:
=IF($A2="";"";IF.ERROR(SUM(FILTER('Base Data'!B:B;REGEXMATCH('Base Data'!$A:$A;$A2)))))
how are you doing?
I need to aggregate ...
0
votes
0answers
17 views
Using a vlookup to pull from a table of a team min and max values for specific stats
So I am working on creating a stat sheet and learning a lot as I go along and using a lot of different tutorials and videos to learn how to write my own formulas, but I seem to be stuck on this one. ...
0
votes
1answer
15 views
Export Google Sheet
I have a Google Sheet that includes custom App Script. I need to:
Download a copy of the sheet.
Zip up the sheet.
Send it to another user.
Is there a way to do this in Google Sheets? For privacy ...
0
votes
0answers
8 views
Using Google Sheets to solve client scheduling problems
I am a small business owner who works by appointment with a fixed number of clientele. Clients come in at different recurring intervals (some once per week, every 2 weeks, every 3, etc) and for ...
0
votes
0answers
15 views
Google sheets, scale Axis to Date, not to item
So i have a simple line chart, it plots a value for each entry into the table it is made from.
Each only has 2 columns:
DateTime, and Value
The value is cumulative, so you expect and upward trending ...
0
votes
0answers
13 views
GSheets : How to find all GOOGLETRANSLATE formulas executed or not from external means? [closed]
I am developing a data processing app using Google Sheets and its API as backend .
In that I will upload all datas as arrays with GOOGLETRANSLATE via batchUpdate API like below
[hello_word,"Hello ...
0
votes
0answers
16 views
Google Sheets Pie Chart - Display Actual Value Below Label, not in pie chart?
I know I can get the actual value to show up in the pie chart, i.e. on the slice. However I want the percentage in the pie chart (which I can get to show up) since it is more likely to fit in a slice ...
0
votes
1answer
17 views
GoogleSheet's Output of IMPORTXML Function Changes Format of Imported ISO Date-Time Text
I have a GoogleSheet that's been working fine using the IMPORTXML function, until recently.
It seems that when the function imports XML data that are in ISO date and time format that it changes the ...
0
votes
1answer
11 views
EXCEL Formula that calculates price after percentage discount, but needs to ignore a "-" to show no discount =IF(I40<>"-",(I39-(I39*I40)))
So I have a formula for adding subtotal after a percentage discount is taken off.
(I39-(I39*I40)
I also have a dropdown list for percentages so 10% 15% 20% and none labeled as -
This is what I have =...
0
votes
1answer
26 views
Google sheets sum cells if corresponding date is in range of a month
I have a sheet in google spreadsheets with every business day in one calender week and the working hours of the staff. Now I am supposed to create a sheet displaying the salary of the employees for ...
0
votes
0answers
47 views
How to list missing values in an unsorted list of value ranges in Google Sheets?
Here is an example of the data in cell A1:
1-3,8,18,9-12,12-15,5-7
What I would like to see in cell B1:
4,16,17
Any help in writing a formula or script would be much appreciated. Thanks!
0
votes
0answers
8 views
I am trying to figure out how to change a column of many dates so that any date that is "less than" 1/1/2021 will automatically change to 1/1/2021
I have the conditional formatting set to change the color of the cell. But I need the actual value to change to 1/1/2021. But only if it's less than. If it's more than that, then I don't want it to ...
0
votes
2answers
42 views
Google Sheets IMPORTXML Issue about Currencies
I've used these link and formula on Google Sheets to auto-update currency:
=IMPORTXML("https://tr.investing.com/currencies/gau-try";"//*[@id='last_last']")
However, it doesn't ...
0
votes
0answers
21 views
A formula to select a percentage of rows that make up a column
I am wanting to divide a column into 1/4ths and have totals calculated on just that fourth, but the amount of rows in that column will be changing. the forths will be simply based on order of ...
0
votes
0answers
7 views
Cell Range in Formula based on Computed Cell Name
I have a formula that finds a cell in a different sheet (of monthly net worth data), and a similar formula that finds the cell that corresponded to the same data 12 months ago.
Example:
=ADDRESS(MATCH(...
0
votes
0answers
14 views
Need helping knowing how to process this data in Google Sheets
I need to sort this data in Google Sheets. I have names and then each name has 4 letters assigned to it, one in each column.
An example is I need to know everyone who has a T and an I in column B and ...
0
votes
0answers
14 views
What's the best way to add helpful files or links to my Google Sheets document?
I want to keep my document clutter free if possible. But it would be nice to add an image link or a PDF link for myself and others to quickly have access to related information. I have tried to add an ...
