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
1
vote
0answers
11 views

Google Data Studio and Slack integration

How Can I connect my Google Data Studio reports, or parts of them with slack? I would like that each day appear in slack some report that I will customize in Google Data Studio. And if I want to do ...
0
votes
1answer
18 views

How can I extract specific information from the cells

How can I extract just the: Brand, Showroom Only Product, etc.
0
votes
1answer
9 views

Custom side menu question & active sheet

I am trying to create a custom side menu that changes depending on what sheet I am on so the side menu is relevant to what I am on. I've attempted doing some code to do a tactic where depending on ...
0
votes
1answer
16 views

Having trouble outputting GOOGLEFINANCE data

My wish is to create a table in Google sheets looking something like this: price day A B C D ... Z 1997-01-02 132.54 . . . . 1997-01-...
0
votes
1answer
11 views

Google Sheets Scatter chart: multiple data points at same X location

I have a dataset where I have on each row data associated with an item that has a name (alpha, beta, gamma in the example), as follows: I now want to create a scatter plot where the X axis ...
0
votes
2answers
26 views

Is it possible to have Google Sheets query function that accepts a dynamic query argument instead of hard coding?

I have a long OR query function that I will be reusing many times in many contexts and that may change frequently. Following the principles of DRY design, I would like to write this query once and ...
1
vote
1answer
13 views

Total Spent Formula in Google Sheets

So with my sheet, I am currently in the process of adding more stats in, I have looked into adding these stats by using Index & Match however it will only match to the first correct match it has ...
0
votes
1answer
14 views

Google Sheet Calculation Only Updates on Return

I am using the formula below. It will only update when I go into the cell and push "enter" to leave the cell. I think it is due to the iferror function. It is calculating correcting but I have to hit ...
1
vote
1answer
46 views

Get last value entered past a specific row

I have been on this a couple of days and I cannot get my head around this ... I use a spreadsheet to track the number of miles traveled by drivers and I want to work out how many miles a driver has ...
1
vote
1answer
10 views

Conditional Output on cell based on 2 different “IF(REGEXMATCH” functions

First of all sorry if I am not clear. it is my first question. I am trying to make a cell give an output with IF(REGEXMATCH on the cell A1 for example =IF(RegExMatch(A2,"YES"),"Good" I also need ...
0
votes
1answer
19 views

Have script ignore first row [duplicate]

I am using this script to add in a date whenever a row is edited. I want it to ignore the first row. Thoughts on how to do this? function onEdit() { var s = SpreadsheetApp.getActiveSheet(); // ...
0
votes
2answers
27 views

If cell y contains any word from range x:x, mark cell y green

In Google Sheets, I have three columns (with different lengths!) Column1 contains firm names (50) Column2 containts firm names (100) I would like to mark a cell in Column1 green (or with a number in ...
1
vote
2answers
19 views

Formula for getting the Left most cell value from a 'Range of cells'

Need your support in finding a solution for an issue which I am facing now. Formula for Column A... Data Range from B to D Have values in a range of spreadsheet cells (From column B to D) Value ...
1
vote
1answer
9 views

Google Sheets forumla help

With my sheet, I need it so that if the job is any type of "Repair" then it minuses 1000 from the business profit but if it's anything else it doesn't minus the 1000 from the business profit. I have ...
0
votes
1answer
19 views

How to merge data from multiple Google Sheets with formula?

I have 10 columns in 10 Google sheets and in all sheets are the data labeled like Month | Total number of winter/summer schools attended | Sr. No. | Title | Date | Venue | Organisation | Number of ...
0
votes
1answer
8 views

Using UNIQUE and SORT together

I'm currently using UNIQUE to look through a spreadsheet and display in another spreadsheet all the unique names in a certain "Name" column. However, I need to sort these names by another column ...
0
votes
1answer
7 views

Is it possible to use a Google Sheets formula to control the value inside of an inserted text box (drawing)?

For aesthetic/design reasons I would like to be able to have the value of a COUNTA calculation be shown as inserted text (ie outside of a cell and can be arbitrarily dragged and dropped). Is this ...
0
votes
0answers
6 views

How to auto populate potential email combinations in Google Sheets

For example I have an email for John Smith and am trying to find out his appropriate middle initial. The email format is smith_john_(middle initial)@company.com but I cannot get the email to auto ...
0
votes
0answers
9 views

How to retain the current style of google sheet to a newly added row from a Google Form

I have setup a google form for a daily task list. And we keep adding new information every data. I have setup a theme and styled the Google Sheet the way I want but when someone adds a new entry from ...
0
votes
1answer
24 views

Calculate hours worked but not limited to a single day

I'm trying to automate getting the number of hours worked for a session, not a day. My raw data is the following: Here is a sample spreadsheet for the data I was able to create a formatted version ...
0
votes
0answers
9 views

Connect a single cell of a Google Sheet to a Google doc

To get data from a google sheet into a google doc where the latter updates when the sheet is changed I can simply select more than one cell, use edit-copy in the sheet and then edit-paste- and choose "...
1
vote
1answer
28 views

How to Update a exactly sheet tab row with a value from a imported data in another tab in Google Sheets?

I have this tab in Google spreadsheet [https://docs.google.com/spreadsheets/d/14ZBff8R6brLYWtfv70V-tyG0HE-sX_ejySvplQqZftM/edit#gid=1450402909] Sheet All Tasks, Candice Tasks, Others People Tasks ...
1
vote
1answer
11 views

Conditional Formatting in Google Sheets not working correctly

I recently tried to edit the conditional formatting for a column that was working correctly before and then change it back to the same exact formula it had before and now it isn't working correctly. ...
1
vote
1answer
23 views

Returning max value based on two conditions

I've been reading through a lot of other solutions to similar problems but am not able to resolve my issues. I have a Google Sheet with two sheets I am working with: Sheet 1 A list of contacts (...
1
vote
1answer
12 views

Rank days of the month that occur most

I would like to know which days of the month customers have historically paid on the most. I would like to create a report that has days 1,2,3,4...31 all with a count next to each day for how many ...
2
votes
1answer
16 views

ArrayFormula to create a table of data from 1 column

I have the below formula and want to turn it into an array. =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1)) I have tried it with the below attempt but only get 1 single cell result. =arrayformula(INDEX($A:$A,...
0
votes
1answer
25 views

How to detect duplicates across multiple sheets?

I have found a couple of formulas/scripts for what I am looking for, but they are all locked for one sheet only, thus I will try to explain what exactly I need. I am unable to do it my self, because ...
0
votes
1answer
12 views

How to automatically add a row above a particular cell?

I'm sending some data from Zapier to Google Sheets and every time Zapier creates a new row, all formulas from that row are gone. I'm looking for a script that would allow the following solution: I ...
-1
votes
0answers
14 views

Google Sheet formula For realtime 20 moving average

Need formula for live and realtime moving average of share price: =average(query(GOOGLEFINANCE("NSE:"&A2,"price",workday(today(),-20),today()-1),"select Col2")) The above formula gives result on ...
0
votes
2answers
13 views

Query twice in the same cell to receive a name next to a number in a row

Sheet link I posted before however, I don't think I was specific enough. I am sorry if I am wasting the time of anyone. In my sheet, you will see three pages. On the "Search Notes" page in C5 I ...
0
votes
1answer
36 views

Unable to change Google Sheets data point format without changing other data points

I am trying to ensure that each bar in a horizontal bar chart in Google Sheets has its own unique color. Conventional wisdom seems to entail either select the chart --> left-click on the bar -->...
2
votes
1answer
14 views

Google Sheets Import Range Query Error

I'm trying to create a sheet that only shows me LOT Codes of specific products from a different workbook that has all of our products and LOT Codes. From what I've seen the Query with Import Range ...
0
votes
1answer
12 views

Google sheet formula for previous 7 day high

Need a formula for previous 7 working day high for a share =INDEX(GOOGLEFINANCE("nse:"&A2, "high", TODAY()-3.3), 2, 2) Used the above formula but I need only workday formula.
-1
votes
0answers
7 views

Google Sheet formula to find Realtime 20 day moving average of a share price [duplicate]

I need a formula for 20 moving average updates whenever refresh it, I used the below formula but it shows day moving average but I need live moving average change as (realtime) share market moves. =...
1
vote
1answer
9 views

Highlight data that match with data from other sheet

I have two tabs in a Spreadsheet. Both are having a list of data. I want to highlight cells in one tab that match with cells from another tab. For example: Tab 1 Column 1 ABC XYZ PQR DEF Tab 2 ...
0
votes
1answer
28 views

Mark Google Sheets comment as done via email?

Is it possible to mark a Google Sheet comment as done from that email you're receiving from <comments-noreply@docs.google.com> if someone adds a new comment for you?
0
votes
1answer
26 views

Is there any way to dynamically modify dates based on number of days something would take?

Imagine you're creating a Google Sheet to track all of your tasks, how long they take, and their deadlines: Task |Number of days| Start Date | Date Done Buy plane tickets | ...
0
votes
0answers
13 views

Podio and Zapier integrations

I am using Podio and Zapier to do some integrations to Google Sheets. Here are the things I wanted to hopefully happen: When a Podio app item is triggered (by a field change), It should update the ...
1
vote
1answer
21 views

Checking whether two columns contain the same string of numbers

Some time ago I have put together a perfectly working formula but now it's not working and I can't tell why (I may have changed something and don't remember that). Column B contains data that I need ...
1
vote
1answer
29 views

SUMIF or IF when sum is more than zero

In Google Sheets, I would like to get - at the bottom-most cell of a column - the sum of each cell (except for row 1, the header) above in that column ONLY if the total is greater than zero, (I don't ...
0
votes
0answers
13 views

Plain text and a hyperlink can be combined in a cell - but I don't know how

This is possible--but I don't know how I did it. When I typed formatted text followed by a URL into cell E2 in the spreadsheet shown below, it magically formatted in a way I didn't know Sheets was ...
0
votes
0answers
8 views

Google Sheets inserting automatically new column for every month

I am about to list all my expense positions (Posicion) for every month. Is there any way that I can automatically add a new column for every month that starts? Some positions are always the same ...
0
votes
2answers
19 views

Query the result of a query in the same cell

I don't know how else to put it of phrase it, I am really stumped here. I will put what I have down below and hopefully, someone will understand what I am getting at. =QUERY(Sheet3!A:B,"select B ...
0
votes
1answer
15 views

Comparing data changes over time?

Let’s say I’ve collected data on a scale of bins from 1-6 at two different time periods (T1 & T2), organized as two columns in Google Sheets: T1 T2 1 2 1 5 2 3 5 ...
0
votes
1answer
14 views

Sending email form Google Sheet Issue

I took this script from the Google developer site and modified it to suit my needs. The issue i am having is where the "EMAIL-SENT" lands on my sheet. I need it to be placed on the 4th column right ...
0
votes
1answer
8 views

Conditional formatting for passed dates

I need the formula to highlight a cell if the date in that cell has passed. Cell U3 had 6/29/19 today is 7/1/19 - I want to have the cell turn red after 6/29 to alert me to follow up.
1
vote
1answer
19 views

GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low

I'm using Google Finance calls in Google Sheets for some calculations. I've got 2 formulas, one to find the low price, and one to find the high price from a certain date: =MIN(INDEX(GOOGLEFINANCE("...
1
vote
1answer
20 views

ImportXML With Line Breaks Google Sheets

I am using ImportXML to get some data from a website which looks like the following: =ImportXML("https://www.papercall.io/events?cfps-scope=&keywords=uk","//div[@class='panel panel-default']") ...
0
votes
1answer
31 views

How can I create a recursive sum of differences?

I am trying to implement a recursive sum of differences - that probably makes little sense. So basically I am keeping track of a vehicle mileages and on a separate tab (sheet) I wish to see the ...
1
vote
1answer
19 views

Sum a column at the bottom of it

In Google Sheets, how do I get - at the bottom-most cell of a column - the sum of each cell (except for row 1, the header) above in that column? Keep in mind, I may add rows in the future.