Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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
38 views

How to find the closest date to today from a list of dates

I have a list of dates on column A. Each day I need to automatically put the date of today or the next closest date on cell B1. 20/8/2018 6/9/2018 25/9/2018 28/9/2018 11/10/2018 30/10/2018 31/10/2018 ...
1
vote
0answers
14 views

getUi.prompt - How to enable confirmation with “Enter” key

When using SpreadsheetApp.getUi().prompt();, how do I allow the user to simply press Enter to confirm their entry instead of having to click on OK?
0
votes
1answer
20 views

Query not working unless I change the Date format to Automatic [duplicate]

Query works fine if I change Hudson F to Automatic, if I change it to date it stops working. I need to view F as date and have the query work =QUERY(Hudson2000!1:6634, "SELECT A, B, C, D, F, G WHERE ...
0
votes
1answer
89 views

How to make Google Sheets jump to closest date to today when opened?

I have a Google sheet containing a list of events, with event dates listed in one column. I'd like to have my Google Sheets spreadsheet jump to the closest date to today's date on opening (i.e., ...
0
votes
2answers
20 views

Google Sheets can't understand timestamp format containing timezone information

GitHub API returns timestamps like so 2018-10-03T10:30:51.444Z which as a programmer is not unusual. But when I put this into a Google sheet cell, it refuses all my attempts to get it recognised as a ...
0
votes
0answers
13 views

Click and drag to edit cells in an existing formula

I have an existing formula. When I click in the formula bar, the specific ranges and cells addressed by that formula are highlighted in different colours. So far, so good. In this case, I want to ...
0
votes
1answer
39 views

Formula for adding sum total from multiple sheets for a specific name

I am stuck in terms of how to set a formula to get the sum total for a specific "name" from multiple sheets.
0
votes
1answer
33 views

How to calculate hours per date

I am trying to use A Google Sheet generated by Google Forms to track total attendance time in lab for a robotics team. I have created a form with the following questions: Checking in or Checking out ...
0
votes
0answers
28 views

How do I get Google Sheets to graph point pairs rather than using the x-index as the x value

I have the following data set: 80, 22.32 90, 24.22 100, 26.36 110, 28.60 120, 30.93 135, 34.10 Where the first value is the X coordinate, and the second value is the Y ...
0
votes
1answer
149 views

Google Sheets conditional formatting for multiple columns

I'd like to set up conditional formatting in columns B:D such that blank cells in B:D are highlighted but only when the same row in A is NOT empty. I think the custom formula should be something ...
1
vote
1answer
32 views

How can I generate a Random Number that does not change in Google Sheets?

I am generating a chart with random data: Date Value 1/1/2018 123 1/2/2018 453 I used RAND to generate the values, but don't want them to recalculate all the time. How to suppress the ...
0
votes
0answers
12 views

Google Sheets Custom Number Format 'Broken'?

I somehow did this when creating a custom number format. Any idea as to how to fix this?
1
vote
0answers
145 views

Google sheets, data validation - how to auto-update the options from a “list in a range”

I have a dropdown in all the cells in a column. Around 150 rows. The four options inside the dropdown come from another tab (sheet), A1 to A4. This works fine. The options are listed from that tab, ...
0
votes
2answers
17 views

Formula that will take a number in a cell, and then add that number to itself -1, until itself -1 = 0

I need a formula that will take a number in a cell, and then add that number to itself -1, until itself -1 = 0. For example, if the number were 5, the formula would be: 5 + 4 + 3 + 2 + 1 I could do ...
1
vote
1answer
34 views

Can I get separate URLs to FilterViews on Google Sheets?

I'm treating a google sheet as a crude DB in which to dump details of all items in an issue-tracker (GitHub). A user can then add manual data in additional columns which must not get overwritten. I ...
1
vote
1answer
31 views

How do I conditionally format text color to match across sheets in Google Sheets?

Edit: I couldn't figure out how to post pics without editing, so I added them to the OP. The first photo is the process form. On the form, as the boxes are checked the color changes from black to red ...
0
votes
1answer
20 views

Only update a cell if it is empty

I have a sheet where column A is "current ETA" and is updated periodically. I want column B to be "initial ETA" so that B takes the first value entered in A, then never changes. Using standard IF ...
1
vote
1answer
305 views

Query: select cells in column where it is not null

I am trying to pull non null data from a column. I have tried the following. Each import range works independently. =query({IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34"); ...
0
votes
2answers
51 views

Get a list of Drive files into a spreadsheet

I am playing around with AwesomeTable and like the Post-It template. My use case is to curate a nice interface to a list of frequently used files. The list won't change dramatically over time, but ...
1
vote
1answer
63 views

Select rows with relevant dates

Scenario At our office we track our time in the following format: DATE | EMPL | HR | COMPANY -----------|------|-----|-------- 09-26-2018 | John | 2 | ABC 09-26-2018 | John | 2 | DEF 09-...
0
votes
0answers
12 views

Zapier + Twitter API + Google Sheets : GET IDs of Users Who Retweeted Me

I'm trying to find out if it is possible to use Zapier to populate a Google Sheet with the IDs of Twitter users who retweet me. I've tried using the advanced search parameters from:username filter:...
0
votes
1answer
136 views

IMPORTDATA In Google Spreadsheet and keep the leading zero

I have a CSV file with certain entries being numbers and start with zeros, such as phone numbers and reference numbers. For instance: "ID","Name","Telephone Number","Tracking Reference" 1,"John","...
0
votes
2answers
18 views

Using SUMIF to add amounts over a given number

I am trying to calculate the total number of overtime hours worked by staff each week. I have a formula that seems to do the job OK, but I feel there could be a better or more concise way to write it....
1
vote
2answers
22 views

When any username on a sheet matches a username on another sheet, copy over certain values into specific columns

I have a sheet with current users of a system (only by username) and I have another sheet that is a master list of username associated with employee first name, last name, and employment status. What ...
0
votes
1answer
25 views

Duplicate values in dynamic sorting

I have an issue with duplicate sorting values for a formula in Google Sheets. I'm using two tables to order 10 scores (see pic). The values in Column D are dragged from another sheet. The values in ...
1
vote
2answers
34 views

Using multiple AND conditions in one IFS fromula

I’m trying to make sheets calculate my delivery times based on which locations the items are shipped between. Tried combining the Ifs and AND formulas, but I keep getting errors. (Let’s say the ...
1
vote
1answer
52 views

In Google Sheets, how do I input a grading scale?

I have a rubric in sheets. It calculates the grade and turns it into an average in two different cells. I want to take the average cell and use a formula (in the next cell) that would plug in the ...
0
votes
0answers
10 views

Google Sheets Conditional Formatting

I'm trying to finish up a Google Sheet with some conditional formatting that I can't figure out. Here's the best description I can come up with. If Column A matches in any row, then the formula ...
0
votes
1answer
79 views

QUERY remove column and column header if blank

The attached document is a time-sheet document. There are 2 tabs, TimesheetData and InvoiceData. I want to pull through data from TimesheetData tab to the InvoiceData tab using a query. The query I ...
0
votes
1answer
16 views

Sum a quantity list in an array

I have an array table using ARRAYFORMULA({Fruit Names, Quantity}) that looks like | Apples | 2 | | Oranges | 3 | | Bananas | 5 | | Oranges | -2 | | Bananas | -1 | | Apples | 1 | I'd like to ...
1
vote
1answer
130 views

Query based on column header in Google Sheets

I am trying to automate Worship list using Google Sheets, so it works good but one thing I can't do can be more than useful. I have a sheet like this: in row 1 dates and below songs used that Sunday....
0
votes
1answer
38 views

How do you delete certain cells before copying?

I want to copy IDs and dates and have written this script so far. function copy(){ var s1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); var s2=SpreadsheetApp....
0
votes
1answer
98 views

Exporting Google SpreadSheet to PDF (filename referring to a cell D7)

I have developed a script which allows me to export a spreadsheet to a pdf file with a click of a button. I have assigned the script to a macro so that it is triggered by clicking on an image. Script ...
2
votes
1answer
66 views

Joining and sorting strings: how to turn week menu into shopping list?

Every Sunday I define with my girlfriend a menu for the coming week. We use Google Sheets for that: each cell is a comma-separated list of products to prepare a meal. We enter those data manually. ...
2
votes
1answer
37 views

Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet with a keyword instead of exact match

I would like to use the importrange formula to import data from "spreadsheetA" that contains "keyword" in columnD. I can get importrange to work but I cannot figure out how to filter it so it will ...
1
vote
1answer
52 views

Conditional Formatting across a range of cells with a formula in Google Sheets

I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018". I have a separate sheet with a data set that has rows that classes end such as "9/24/2018". I want to ...
1
vote
1answer
180 views

IMPORTRANGE syntax when using query on multiple tabs

I am using IMPORTRANGE within a query to extract data from sheet 1 to sheet 2. The following code is in a cell in tab A of sheet 2, and works as intended: =query(importrange("URL-A","E:J"), "select * ...
0
votes
0answers
35 views

Google Sheets Combining multiple IF statements into one formula (as an Array)

Hope someone can help, got the formula below but I want to modify it so that the first IF formula, IF(F3:F="Delay","Delay", returns a “Delay” if it finds a match (not if it finds "Delay") and if it ...
0
votes
1answer
12 views

Conditional formatting based on another Sheet

I have a column on sheet1 with a list of names. I need conditional formatting on it to turn it red if that name does not show up anywhere on sheet2.
0
votes
1answer
45 views

How can I create a filter view with MOD custom formula?

I want to split a spreadsheet tab (called main) so to have (say) N = 3 people working on it without interfering with each other. For the purpose, I thought to create 3 filter views, each containing ...
0
votes
1answer
198 views

How to concatenate columns for multiple rows simultaneously in Google Sheets?

I have the following 3 rows of data in cells A1:A3 in Google Sheets: Doe, Jane Doe, Joe John Doe I want the following in B1:B3: Jane Doe Joe Doe John Doe Essentially, I'm creating a new column ...
0
votes
1answer
143 views

Google Sheets - Importrange

Currently we have one summary sheet called Order_Summary which collects data, one day per column and someone at work creates a new column daily. We now have another google sheet which now records an ...
0
votes
0answers
12 views

Count Seconds on Sheet Update

When you change any value in a cell, some functions automatically generate new update. One of these functions is NOW(). I currently have =SECOND(NOW()) in A2. I also have a checkbox made with data ...
0
votes
1answer
16 views

How to create a 3 column chart in Google Sheets with one numerical column and two text columns with customization?

Consider this data: +---------+----------+-----------+--+--+ | Food | Quantity | Type | | | +---------+----------+-----------+--+--+ | Apple | 3 | Fruit | | | +---------+-----...
0
votes
0answers
11 views

Range as a Sheet

I am trying to use Google site and Google sheet so that as people put in an application, it goes to a Spreadsheet & that range on the sheet then becomes its own sheet which I can link to the site. ...
0
votes
0answers
55 views

Google sheets script copying formatting *but not* merged cell status

I would like to write a script that copies the formatting, and the conditional formatting, that is applied to cell A1, and applies it to all of the other cells in the sheet. However, I do not want ...
0
votes
0answers
11 views

Conditional choices from a list

I have 8 different fee rates in a spreadsheet. Only one will be chosen from the list. I want to find a total of various fees that includes one of these. Is there a way to right a conditional without ...
0
votes
0answers
18 views

Can you link a cell to show data from a specific part of a PDF document?

I work in the inventory department of an auto-repair shop, and I'm trying to set up my Google Sheet document so the prices are always updated with the prices on the manufacturer's website so I don't ...
0
votes
1answer
47 views

How can I make a hyperlink from Google Spreadsheet cell to an Excel file?

How can I make a hyperlink from Google Spreadsheet cell to an Excel file? For example the hyperlink is in Google Spreadsheet and opens the Excel file. The Excel file is on the hard disk not on Google ...
0
votes
1answer
40 views

Can you set a cell to have a price on it linked to the main website?

I work in the parts department of an auto-repair shop and have been charged with organizing everything and creating an inventory sheet. I have done all of that and am now trying to improve the system ...