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
0answers
2 views

How to customise ordering of segments in stacked waterfall chart using Google Sheets

I've drafted a stacked waterfall chart in Google Sheets (shown below). By default, the ordering of stack segments are as follows: In Column A: Apples, Bananas, Carrots, Durian from top to bottom In ...
0
votes
0answers
2 views

Unable to position custom data labels in center in Google Sheets waterfall chart

Context I've drafted a stacked waterfall chart in Google Sheets, with custom data labels (guided by Ben Collin's tutorial on custom data labels). My goal is to have the custom data label positioned ...
0
votes
0answers
3 views

Google Sheets Data Feed has extra column headers in the JSON

I have a client who is using Google Sheets as a data feed. When using the correctly structured URL for the sheet, I see blank columns in the target table. When I look in the code inspector, I see gsx ...
0
votes
0answers
8 views

Match based on row with lowest value

I'm finding the index of a row using another text value in a row as a reference to add a value to a "unit cost" column: =INDIRECT("Sources!I"&MATCH(A5, Sources!A:A, 0))*CONVERT(C5,D5,INDIRECT("...
0
votes
0answers
7 views

From Google Form to sheet to doc

I have made a form to sign people up for an outdoor club. Info includes their name, address, equipment they have etc. As the activity is a group activity and people will be meeting from all over the ...
0
votes
0answers
10 views

Using Google Forms to create an automatically updating inventory

I have created a Google Form that is used for people in the office to request items (swag given to donors). After an item or items are requested, the request must be fulfilled by a team member. I ...
0
votes
0answers
7 views

Google Sheets ImportHTML formula not pulling table I need [duplicate]

I have a Google Sheet trying to pull website table using ImportHTML formula to get a table from https://mesowest.utah.edu/cgi-bin/droman/raws_ca_monitor.cgi?state=AZ&rawsflag=3 ImportHTML(https:...
-1
votes
0answers
9 views

How do i implement importrange the formula below?

can anybody here share with me how to actually implement importrange into this formula.. =SUMPRODUCT(SUMIF(INDIRECT("HON_"&Parameters!$B$7&"_"&YEAR(Parameters!$A$2)&"-"&MONTH(...
2
votes
1answer
13 views

Conditional Formating for Google Sheet comparing to list of text (highlight duplicates)

I am trying to create a custom formula for conditional formatting. I want to highlight the cell if the text appears in a list of names. This list will contain 210 unique names so I can't type each one ...
1
vote
1answer
14 views

How to countif with dates before today's date that auto updates

I am trying to use countif to count a certain number prior to today's date. So if today's date is 8/15/2019 the formula would count the number that is the B column that had a corresponding date prior ...
0
votes
0answers
10 views

Select Column A Where values in Column A are not present in Columns D-G

Having trouble with a query. I want the query to select column A where values in Column A are not present in a Columns D-G. I have attempted a filter and had no success.
0
votes
1answer
15 views

google sheets query function

google sheets query function Trying to use the following query: =query(WABrdg729!A:B, "select A, B where A > date '"&text(datevalue($A$6), "yyyy-mm-dd")&"' AND A > timeofday '"&text(...
0
votes
1answer
12 views

Conditional Formatting in Google Sheets based on the same rows conditional format

I have a spreadsheet where I have tasks, and I prioritize those tasks based on high, medium, and low levels. I currently use conditional formatting for this, so if I select from a drop-down "HIGH", ...
0
votes
1answer
12 views

Sequential numbers via formula - Google sheets - IF & MAX

I use this formula to find highest value in column A of other sheet, and then add 1 to it to get new unique number for a record ID, and I use... =if(B3<>"",max(Backend!A3:A)+1,) with this ...
0
votes
0answers
6 views

Automatically copy new imported entries into another sheet

So I'm using importjson to import a json feed into my google sheets. =importjson("url") It updates with 10 new entries a day. However, it doesn't save old entries, it just updates with new ones. ...
0
votes
0answers
16 views

Race results as race is being run

i have a sheet for a multi lap race. At any point I want to print the current standings. Racer D is currently leading having gone the furthest, racer B in 2nd, and so on. How can I get the current ...
0
votes
0answers
21 views

Google Sheets adding 20+ thousand empty rows automatically

Trying to expand a google sheet as necessary as I fill in data, but for some reason and with no explanation the sheet suddenly adds rows up to 25,000 while I'm still only on row 600. Is there some ...
0
votes
1answer
11 views

Google Sheets - summing numbers across a spreadsheet depending on the Column header

I am looking to automate our timesheet process. Each staff member has their own separate Google Sheet which has the dates down one column, and then a varying number of columns named after different ...
1
vote
1answer
15 views

Concatenation within IMPORTRANGE

I'm completely stuck on this one -- I need to use IMPORTRANGE function but want to make the sheet it pulls from flexible via concatenation -- here is my formula with a scrubbed URL: =importrange("...
0
votes
0answers
34 views

Open Gmail compose window in same window if clicked on Google Sheets comment reply?

Below is an example of an email received from Google Sheets comments. If I click on "Reply", the compose window opens in a new browser tab. Is there any way to make this compose window open in the ...
1
vote
0answers
10 views

How do you “Paint Format” more than once in Google Sheets?

In Google Sheets you can select a formatted row, click the "Paint Format" Tool, then click another row to apply the same format. You can drag down to do multiple rows if they are together (say rows 50-...
1
vote
1answer
33 views

Create table showing difference between values in two pivot tables

Our bookstore has two separate documents in Google Sheets (one containing purchase data and one with sales data). Each document contains a sheet with a pivot table that sums up total purchases/sales ...
0
votes
1answer
9 views

How can I group rows in Google Sheets and sort

I have been trying to group rows in Google Sheets. I have a heading (name) and then the information that must be included on the sheet without losing the spacing or lines. How can I do this ...
1
vote
1answer
19 views

Extracting specific phrases from a cell and echoing them in another

Longtime learner, first time poster here! I was wondering if anyone knew how to extract specific data points (phrases) from a Google Sheets cell and echo them in another cell. For instance, in my ...
0
votes
0answers
8 views

I need help auto-incrementing a column in google sheets based on data in another column

I'm working on a spreadsheet linked to a google form and I need to generate an ID for specific response. The form has 2 types of entries: New Entry and Update Entry. I need to assign an ID to the New ...
0
votes
0answers
11 views

Time based trigger - Google sheets

I have come across this code for a side bar, and its pretty neat. If I click a cell, the sidebar updates with the records. Which bit of the code is doing this? (Theres 4 files on below link and ...
-1
votes
0answers
24 views

Google Script to find last sent/received email timestamp from Gmail [on hold]

ISO Google Sheets Script to Use Sheets' Email Column to search my Gmail, Find the last Sent message to that email and add it to "Sent Timestamp Column" Find the last Received message from that ...
0
votes
0answers
7 views

How to make one cell automatically update a timestamp every time another referenced cell is updated?

I want to make a cell in my spreadsheet that updates a timestamp every time another cell is updated. I have a SUMIF connected to a budget inflows/outflows tab in my spreadsheet and I want this cell ...
1
vote
1answer
8 views

Setting a trigger to send mail when data is added to a row that was empty

I`ve got an external app that sometimes adds data to the last row of a sheet, is there a way to trigger this to send a mail with the content of that row?
1
vote
1answer
10 views

How to generate a range of serial numbers by formula?

I want to produce an array of numbers from 1 to n, where n is a value in another cell. So if the cell value is 10, I want to generate an array of the numbers 1 to 10. I found this resource which ...
0
votes
1answer
17 views

Syncing Google Sheets to Google Calendar

Automatically add a schedule from Google Sheets into Calendar I followed the instructions in this video: https://www.youtube.com/watch?v=MOggwSls7xQ and it works fine. However, the one thing that ...
0
votes
2answers
28 views

Chronological ordering of checkbox in Google Forms

I have a checkbox in my Google Form, which sends the user selections in the form in an ascending order list: But I want to grab the list in the order of selection, for example if the user clicks in ...
0
votes
0answers
29 views

How do I add values from one sheet based on a dropdown that references the other?

I have 2 sheets with the same columns with integer values. In sheet A I have a drop down list of the names in sheet B. Based on what drop down I have selected, I'd like to add the values of sheet B to ...
1
vote
1answer
14 views

How to highlight cell if it's value is a multiple of a given number

I would like to apply conditional formatting to any cell within a range containing a value that is a multiple of itself. IE: Apply bold font to any cell of column A that is a multiple of 4 (4,8,12,16,...
0
votes
1answer
19 views

Reference an array's data in Google Sheets

Per Google Docs Editors Help > Using arrays in Google Sheets: Any function that takes a range (e.g. A1:B6) as an input parameter will also accept an array in its place. For example, SPARKLINE ...
1
vote
2answers
35 views

Issues with Google Script on collecting Form responses and moving to another sheet

I have been trying to piece together some code for a task at work using Google Sheets and I am having some issues with what I can find versus my current knowledge level. I am trying to create a script ...
-1
votes
0answers
18 views

How to implement inheritance in a Google sheet

I have a sheet in Google Sheets that has several complex formulas that will be used many times over by other sheets. Additionally, improvements are regularly being made to these formulas. As of now, I ...
1
vote
1answer
27 views

Google Sheets - Split row into multiple rows

Current: ------------------------------------------------------------------- | Amy | Adam | Bob| Cliff | Dave | David | Derek |...| Zachary ------------------------------------------------------------...
1
vote
1answer
24 views

Conditional row sum

I'd like to sum a column of cells if the corresponding cell on the next column contains a string of any sort. I've tried using SUMIF but I can't figure out how to combine it with ISBLANK or ISTEXT. ...
-1
votes
1answer
21 views

Google Spreadsheet Trigger which will fire when the active selection changes for a given sheet

This would be equivalent to the Excel Worksheet_SelectionChange event.
0
votes
1answer
27 views

Help with complicated math and syntax

I am trying to create a calculator for this mobile game that I play (Lord’s Mobile) but cant seem to figure out the formula. 1) In this game, you can get your upgrade time reduced when your guild ...
1
vote
0answers
25 views

In Google sheets is it possible to have a combined chart with stacked columns?

I currently have a simple combined chart with a revenue line and columns. For what I am trying to communicate with this graph, I would really like to have stacked columns in addition to my revenue ...
1
vote
1answer
53 views

Send a Google Forms response to specific email address based on form response

I need to send Google Forms responses to specific clinicians at my practice depending on whether they have been selected on a drop-down menu in the form. At the moment, Google Forms sends an email to ...
1
vote
1answer
27 views

How can I select all cells that contain specific text in Google Sheets?

I have a spreadsheet that contains 20,000 cells. I want to select all cells that contain "foobar". How can I do this?
1
vote
1answer
19 views

Querying for the last results with group by

I've got the following data sheet: date(A) source(B) cost(C) totals(D) val(E) 9-8-2019 Magic 0.00 0 0.00 9-8-2019 Stick 54.72 6820 ...
1
vote
0answers
26 views

Conditionally format a column if it contains all the values in a set

Each of the columns in my Google Sheet represent a farm. Each farm is a list of animals. I want to highlight the entire column if the farm has a duck, a horse, and a cow. So the column may look like ...
1
vote
2answers
60 views

Is there a way to allow users to access Google Sheets with installable triggers without manually signing in?

I have set up a Google Apps Script that validates data that is entered into various Google Sheets files. I am looking for a way to allow users to access those Google Sheets files without making them ...
1
vote
1answer
17 views

How do I keep borders when dragging down to fill cells?

I have a large scheduling spreadsheet of dates and names, split up by week, with borders for clarity. I want to be able to autofill the dates by dragging down the cell. Sometimes I also want to fill ...
0
votes
1answer
27 views

Google Side bar & editing form responses

I have some code which captures the URL of the edit response, so you can click it, and amend the record if needed. It pops out and opens in a new window. I wondered if it was possible to open it ...
1
vote
1answer
20 views

“Superset” Checkbox

I have two checkbox columns E and F. I want F to be the "superset" checkbox column, meaning that every checked cell in E means the corresponding cell in F must be checked as well. I can populate ...