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

Google Query, change select columns based on cell reference?

I am trying to improve a manual process that requires filtering through the input from a Google Form. It would be great if we could filter each question individually, without building multiple queries....
4
votes
1answer
474 views

Programmatically create an internal hyperlink

I need to programmatically create a large number of links in a Google Sheets that point to a specific range in another sheet of the same document. I tried to use named ranges for that but for the ...
0
votes
0answers
7 views

Recalculation of references after Copy in Google SpreadSheet

I have the problem of the lost references after a copy in Google Spreadsheet that has been solved in that post with the "Find and Replace" of "=". But is there anyway to do that trick in recalculation ...
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
2answers
32 views

Conditional Formatting based on other column

I have a timesheet log with columns DATE | HOURS | DESCRIPTION OF WORK | PROJECT An employee may enter several rows for the same date to log the different amount of time with respect to the ...
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
0answers
6 views

Google Sheets not processing very large numbers

I have a Google Sheet I setup for my clan, and we need to put in very large numbers. We are already using the Scientific Notation for the numbers, but one very large number is not getting treated the ...
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
15 views

Add numbers to results of Google Sheets query

Is it possible to add a column of numbers to the results of a query? It would be nice to have a number (from 1 to n) next to each records returned from a query.
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....
0
votes
0answers
14 views

Dialogue Box Input w/o click (just enter) in Google Sheets

I want to be able to use a barcode scanner to enter data in a dialogue box in Google Sheets. The barcode scanner inserts "enter" after the numbers. How can I make the enter trigger the data entry (...
0
votes
0answers
10 views

Calculate Percentage with QUERY on Google Sheets

I have a Google Sheets database like this: Name | Value | Answer A | 100 | Y B | 200 | N C | 10 | Y A | 100 | N A | 1000 | Y B |...
0
votes
0answers
6 views

Can I hide the dropdown arrows from Data Validation in Google Sheets?

I use Data Validation with List from a range set up on all my rows in a column to be able to choose a "Category" The arrows however, are annoying to see - especially on cells I haven't yet filled. ...
0
votes
0answers
60 views

Google sheet download as XLSX or CSV are not displaying the data values

I'm trying to download google sheet with values (All looks good on drive) Historical Data =MIN(GOOGLEFINANCE("GOOG","price","3/1/2018",,"DAILY")) =INDEX(GOOGLEFINANCE("GOOG","price","3/1/2018",,"...
4
votes
1answer
3k views

Auto-create and name new sheet in Google sheets from names being filled from a Google form

How can I automatically create and name new sheets in a Google Spreadsheet from a list of names which are being input in the sheet from a Google Form? I would like each new sheet to be created when a ...
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 ...
0
votes
1answer
10 views

How to use 3 separate sheet specific onEdit formulas?

So I have 3 separate sheets in on Google Sheet Doc, I have a script running on the first sheet that hides a row when a drop down is ammended: var SHEET = "Tweaks Needed"; var VALUE = "All Tweaks Made"...
0
votes
1answer
23 views

Count unique tags

I’m trying to list and count all tags in my table. For some reason, anything after comma, is getting a white space before the word. So if the tag is found on the beginning of a record, it is seen as ...
5
votes
2answers
756 views

Coloring cells according to the value to which its content is closest

I am trying to make a guide for my dragons on a game I play but I'm not quite sure how to code what I am trying to do. Link Example: if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its ...
0
votes
1answer
51 views

Google Sheets intersection query

I'm trying to write an intersection query in Google Sheets that lists all the rows in sheet 1 that contain a cell in sheet 1, column A, that matches at least one cell in sheet 2, column P This is the ...
0
votes
1answer
20 views

How can I use a script across multiple pages of a Google Sheet

I'll start by saying I'm very new to this. Here's my problem. I have a GoogleSheet I created to use as a seating chart in my classes. It is designed so that when I click the blue check button the cell ...
2
votes
1answer
512 views

Put the timestamp of last edit on master sheet, next to edited sheet's name

I'm trying to come up with a script that will print a timestamp of the last edit made to a specific tab within a google sheet. Imagine you have 17 retail stores, each one having its own tab with a set ...
0
votes
1answer
253 views

Steam Webapi & Hours played for a specific game - Google Sheets

So I have managed to get a list of owned games and total hours in each game with =importJson. The only issue is I only want data for one game. The json query returns all owned games. The api wiki says ...
0
votes
1answer
19 views

Replacing Query output string when the count is less than 3

Given the reference table below: Date Location 11/29/2018 Office A 11/29/2018 Office B 11/27/2018 Office C 11/27/2018 Office A 11/27/2018 Office A 11/24/2018 Office C 11/24/2018 Office ...
-1
votes
0answers
10 views

Google Sheet using dialogue box as interface to collect data AND open again for next data

I'm building an electronic sign-in sheet for students at my School Library. I'd like students to enter student number (using barcode), username, or last/first receive feedback that it worked by ...
0
votes
1answer
12 views

Splitting multi-line cells into rows

I'd like to be able to split multi-lined cells into rows using only 1 command. I have tried using the command =transpose(split(A2," ")) but when trying to add the 'tab' key as a custom separator ...
0
votes
0answers
9 views

Is there a way to conditionally delete columns in Google Spreadsheet?

I have a large spreadsheet with hundreds of columns. About half of the column headings have the word "alone" in them. I need to delete all of the columns that contain "alone" in the header row. Is ...
0
votes
1answer
225 views

IMPORTRANGE row limit?

I have a document where I am trying to mirror some data from one Google sheet to another. I have over 1000 rows in the first document; when I use the IMPORTRANGE formula it works fine as long as the ...
0
votes
1answer
10 views

Google Sheets: Freeze vs Protect?

I have a sheet (screenshot attached) . The first 3 rows are header rows and are frozen . I noticed that a formula that I'd put into U3 in the header =ARRAYFORMULA({"days since";IFERROR(IF(V4:V*1<&...
2
votes
1answer
5k views

Auto-Find and Replace in Google Sheets with Scripts

I am having trouble creating a script that will find the value 'MKE' when entered into column C, and replace it with 'Milwaukee, WI'. Basically, I have addresses being placed into column C and would ...
0
votes
1answer
41 views

Absolute References

I've been looking for answers to my need, but seem to keep finding answers to the opposite problem. Here is my challenge. I have a google sheet to process payroll. On the first tab is data ...
0
votes
1answer
245 views

Transposing with header row

In Google Sheets I'm trying to transpose a list of items in rows to display as columns. I am looking for a way to transpose the Category field to act as a header row that extends over all the items of ...
1
vote
1answer
21 views

How to calculate years left in Google Sheets

This is the function I used and it work =YEAR(A1)-YEAR(B1), A1 is today B1 is the date in the future or past. The problem is that I want to round up year and months. This is the exaple: https://imgur....
1
vote
1answer
367 views

Conditional format custom formula in Google Sheets

Have a question on how to do a custom formula in conditional format for the following: A1 = DCC220 B1 = 7.50 C1 = 7.00 D1 = 7.60 I want to compare the result in B1 (7.50) to a range, C1 lower, D1 ...
0
votes
1answer
10 views

Google Sheets get row number of visible row

I want to get the row number of the first visible row on one of my sheets. So for example if rows 1 - 499 were hidden then I want to write a formula that returns the numerical value 500. I know this ...
0
votes
1answer
18 views

How to summary the multiple entry into a single sheet by name and by date. Name is in Row and Dates are is in column

Sheet 1: Name 1-Jun-18 2-Jun-18 3-Jun-18....... KB CL KB EL AJ SL SD SL SD CL I need this data to be like below table in Sheet 2: ...
0
votes
1answer
17 views

Sum by month and then by month and category formula

Cross-post I've been trying to figure this out for weeks. It's a spreadsheet to help people understand their spending habits. They enter their monthly expenses on one sheet, and the other ...
0
votes
0answers
15 views

Google Sheets Import Price from Website

Could anyone help please?, I would like to extract the price from the following webpage? https://www.bricklink.com/v2/catalog/catalogitem.page?M=sw822#T=P Ideally looking at returning the Min,Avg &...
0
votes
1answer
9 views

How to get data from a different sheet based one the adjacent sheet in Google Sheets?

How would I change the sheet name in B3 when the name is changed in A3? Could someone please assist me with this?
1
vote
2answers
63 views

Querying if cells exist in an external Google Drive file

I have a huge list of names stored in Google Drive as a TXT file (constantly updated externally). Can I put a small list of names in a Google Sheet and see if any of them exist in the big external ...
0
votes
2answers
2k views

How to format the time in this Google Apps Script

I want to format a Google Sheet cell when I read it in my script but it comes out 25 minutes later. The cell's time is 20:00:00 but the output is "20:25". I am using this var formattedDate = ...
1
vote
1answer
50 views

Collated and separated results in one cell for QUERY results across multiple columns

In a Google Sheet I have the following formula: =CONCATENATE(QUERY(Summary!I4:U200,"Select I,U where S='"&A3&"'")) This currently returns each pair of results in sequence (with no spaces) ...
0
votes
1answer
23 views

How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets

I am trying to remove duplicates from my Google spreadsheet file. Here is an initial table example A B C D HelloWorld1 2 6 41 HelloWorld1 4 3 5 HelloWorld2 6 46 6 ...
0
votes
1answer
26 views

Creating a script for Google Sheets to send an email if cells in a row contain a certain value

I have a Google spreadsheet that I created to track daily equipment inspections. The data in the sheet comes from a form that is filled out by whoever inspects the equipment for that day. Here's a ...
1
vote
0answers
14 views

Force Google Sheets to round up to specific fractions

I'm trying to format a google sheet to calculate the amount of raw lumber I need to purchase for a given project. Raw lumber is sold in quarters, so for example 4/4 is a one inch board, 5/4 is an ...
1
vote
1answer
33 views

Google Forms to spreadsheet edit responses and new responses on the top

I've created a Google form and I want to be able to drag the rows into categories below it, but I want the new responses to always show up at the top. Instead, it follows the previous row. For ...
0
votes
2answers
17 views

Duplicating sheets with simple date from a named range

When I run the script to duplicate sheets with a date is give the tab a long date name Thu Oct 18 2018 00:00:00 GMT-0400 (EDT). I would like it to read in a 10/18/18 format.
6
votes
1answer
11k views

Google Sheets auto-format the highest value in a row of cells

I have a set of data like so: A|B|C|D 1 9|3|5|4 2 7|6|6|6 3 3|6|2|7 4 6|9|4|7 And I would like to auto-format the highest value in each row. So, for instance in row 1, A1 would get the auto-format,...
0
votes
1answer
15 views

Need to trigger form submit when data is already populated in Google Sheet from another source

We have tried many thing to do a job basically we have a Google Sheet to collect information from Google Form once the form is submitted the data get inserted into the sheet and document studio start ...
1
vote
2answers
2k views

COUNTIFS with multiple OR

I have two sheets, one is a dumpsheet of values like below: and the second sheet is using values from this to total. I am trying to get the total count for the corresponding zone and test name but ...