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

Random grey horizontal lines in Google Sheets

I'm having the exact same problem as in this post, but the fix didn't work for me: Random gray horizontal lines in Google Sheets - using Chrome and Opera I'm only using chrome. I'm on 100% zoom.
2
votes
1answer
18 views

How to make a cell highlight red if its less than 6 digits and NOT effect blank cells?

I use this function =LEN(P3:P)<6 in order to highlight all the entries with less than 6 digits (6 is minimum) However, all the blank cells also highlight red. What function do I have to do in ...
0
votes
0answers
10 views

In Google Sheets script how do you email a date in a cell and control how it looks in email?

I'm building a Sheets Script add-on for sending invoices. In my sheet, I have a chart with the dates written along a row. When I use the MailApp.sendEmail() the date is printed in the email ...
0
votes
0answers
9 views

Auto increment date in sheets (for daily reports)

I have to manage a daily report sheet in which I have created a template/Format which is copied or duplicated by my team members every day. In the date col, I want the date to automatically update ...
0
votes
3answers
63 views

Trying to run two scripts on same sheet and getting error

I found a code to run the two different scripts online, however i keep getting an error Missing ; before statement. Original function onEdit(e) { oneditfx1(e); oneditfx2(); } function ...
0
votes
0answers
14 views

Code to automatically duplicate a google spreadsheet

I want to automatically create a copy of an existing google spreadsheet that will have a name taken from a list. So when I add a name to that list the file will automatically duplicate with a specific ...
0
votes
0answers
16 views

How to move a row to another sheet based on date value

I want to move row to another tab based on date value. i.e. if the data is 90 days older from today the row should automatically transferred to another tab. Sample Sheet: https://docs.google.com/...
0
votes
2answers
21 views

Viewing & editing data in Google Sheets using a form

Is there any way to view and edit data in Google Sheets in a form, like MS Access or OpenOffice Base can do that? It would be perfect if we could cycle through each record, which would be the ...
1
vote
1answer
19 views

AND / OR Conditional Formatting for Google Sheets with multiple criteria

I am trying to write a custom formula to conditional format column E by referring to the different tenure level targets in column D to indicate if they are hitting their targets base off of their ...
0
votes
1answer
34 views

How to reference a dynamic range in Google Sheets Script?

I have a sheet where the filter originally started on row 2, so my range was A2:J. As I've added and deleted rows/columns, my range has changed and it's still changing as I edit the sheet. Is there ...
1
vote
1answer
25 views

Query sum for unique pairs of rows

I have a spreadsheet which logs the duration an employee spends in a city. An employee can visit a city multiple times like Peter in this example. City Person Days London Tom ...
0
votes
1answer
28 views

Cut and paste data from a row of cells from one tab to another tab in Google Sheets and repeat with new data

The below code works for Q3 but not for Q5. I'm relatively new to sheets and writing code and I'm sure that you can't have two functions with the same name but am unsure how to resolve so that I can ...
1
vote
0answers
13 views

Update other sheet's rows based on pasted data [closed]

I have a worksheet with several sheets in it for social media analytics purposes. I would like to be able to paste data from a CSV I export daily into one sheet and have the data parsed and copied to ...
1
vote
0answers
15 views

How can I send an email, with an attachment from the user's local storage, using Google Sheets Script?

I'm new to Google Script but quite experienced in Excel VBA. I'm trying to set up a Google Sheet to enable sending of a given document by email attachment to a number of recipients. I've got the email ...
0
votes
0answers
19 views

Copy the data from one sheet to another, assigned to a particular line

I have two google sheets. I need to copy the data from one sheet to another, assigned to a particular line. For the sake of ease of understanding, I changed the colors in the sheets example as they ...
0
votes
1answer
16 views

Pull data from another apreadsheet where Row and Column are strings

I am trying to pull data from a different sheet in google sheets by matching row and column header EG SheetB LOC prox dist qty sjc 12 15 19 dfw 10 16 8 atl ord In ...
0
votes
1answer
12 views

Countifs in between ranges

I'm struggling to pull below conditions together. I'd like to have a formula that will count values between 1 and 5 only when cell U13 contains the text 1-5. =COUNTIFS(U13,"1-5") =COUNTIFS(K2:K22,"&...
0
votes
1answer
19 views

I want to get values from multiple URL's in Google Spreadsheet. IMPORTDATA doesnt work

I have 9000 lines with URL's in my Google Sheet. Example: https://steamcommunity.com/auction/ajaxgetgoovalueforitemtype/?appid=233860&item_type=10). Every URL returns JSON like this: { "success": ...
0
votes
1answer
23 views

How to partial extract from 3 columns with multiple levels of rules?

The formula someone on this forum provided worked well, but I have run into a problem, which I am struggling to get around. Google sheets Link The formula in H2 looks between 00 & 02 and ...
0
votes
1answer
22 views

Google sheets spreadsheet will not format date correctly

I have a Google spreadsheet with a date column formatted to dd mmm yyyy. Many of the dates are formatted this way, but some cells will not format this way, and no matter what I select under "Format", "...
0
votes
0answers
11 views

hide rows in Google Sheets based on cell value in a different row

I'm trying to build a worksheet in google sheets with a checkbox which will hide and unhide lines 5 thru 60. My checkbox is in cell A4, and when checked cell A4 says "hide", and unchecked cell A4 ...
1
vote
1answer
37 views

ArrayFormula to extract part of the string

I have come across the below formula, and I am finding it quite useful, I want to turn it into an array shortly, but before I do, I was wondering if there was an easier way to do this? =if(len(LEFT(...
1
vote
1answer
18 views

When I insert a row, indirect does not update

I have a summary sheet and I would like that every time I add a new sheet everything gets referenced to the summary sheet. I have used indirect to do this, the only flaw is that once I add a row, the ...
0
votes
1answer
13 views

Simplify fractional result of custom number format

In Google Sheets I would like to express any decimal number to the nearest 32nd of an inch. If I type 1.537 I want the result to be 1 17/32, but if I type 1.5 I need it to be expressed as 1 1/2 not 1 ...
0
votes
1answer
19 views

Unsimplified Fractions in Google Sheets

How can I get Google Sheets to display fractions in the form I enter them, rather than in a simplified form? I'm trying to evaluate how a student did on a test, so I have one cell that reports the ...
-1
votes
2answers
15 views

How to archive cell changes in new tab?

I have a Google sheet that I'm collecting some statistics in. There is a particular cell that I'd like to track changes in a separate tab. I'm looking for a function to copy cell if the cell data ...
0
votes
2answers
21 views

Help with a Vlookup function to return multiple results of not exact match

I am trying to run the following code to return all the matches of column A from column C in a set of rows. In this situation, cell A1, is contained in a few iterations of column C, however, these are ...
1
vote
2answers
41 views

Combining IMPORTRANGE, SUMIFS, QUERY with cells that contain numbers and text

I'm having an issue combining SUMIFS with ImportRange in Google Sheets. I found answers suggesting to nest it with QUERY. However, the issue is that one of my criteria is non-numerical. Formula in ...
0
votes
1answer
15 views

Conditional formatting skipping colors after the same large value

Looks like the conditional formatting skipping large formula because of the same large values. Help me please how can I get the order what is on the picture in the column "What I would like".
0
votes
1answer
18 views

Google sheets IF OR script

So this is the code that I'm using for the script that I found; function onOpen() { var s = SpreadsheetApp.getActive().getSheetByName('Todo'); s.showRows(1, s.getMaxRows()); s.getRange('D:D') ...
0
votes
1answer
10 views

Process visualization based on data

I am trying to use a Google spreadsheet to visualize a customer journey. I am trying to create the following graphic: I have created the following example spreadsheet: https://docs.google.com/...
1
vote
1answer
32 views

Simplify multiple OR statements in a QUERY using IMPORTRANGE

I am trying to query values from a different sheet using a range from the current sheet. Is there a way to simplify the formula and get rid of all the ORs? Like maybe using a FOR loop? =QUERY(...
1
vote
0answers
9 views

Conditional Formatting formula for alternating checkerboard with Merged Cells?

I wrote a formula that attempts to create a checkerboard pattern via conditional formatting, that should even work with Merged Cells. However, it seems to fail in an almost undebuggable manner with ...
1
vote
1answer
17 views

How to reference a local cell in QUERY?

I just about have formula 100% perfect. Just one issue: Formula (Works): =QUERY( ImportRange( "https://docs.google.com/spreadsheets/d/1Lf4r_b0fmFh6fClZsGmfZmo8ZD87qf8N_0f-Jaoyu-g/edit#gid=0", "...
2
votes
1answer
19 views

Google Sheets date conditional formatting

I have dates in column D in Google Sheets. I want the cells to highlight red if 30 days have passed and there is not a comment date in column F. Example: Column D: Submittal Date 6/29/2019 ...
1
vote
1answer
19 views

Array formula for adding letters after duplicate numbers in order

The below formula adds a letter after a sequential number, A, B, C, D, etc for the same number until it finds a new number, then it starts again from A. I need help on turning this into an array ...
1
vote
1answer
23 views

How to extract the first rows that have a unique value in specific column in Google Sheets?

The unique items in D are: 4,3,2. I wish to extract 3 rows for each D value, For each row, provide the first row that this D value appears. I tried using =query but it doesn't support grouping or ...
0
votes
2answers
26 views

Format Cells if greater than “value of the cell”

Values of K11–K13 are variables. On the Conditional formatting, I want to use the output from those cells in the "Greater than" function. Typing =k11 doesn't work. I'm not really sure on how to do it. ...
2
votes
1answer
17 views

Which filename extensions are NOT safe to move outside of a syncing Google Drive folder? (because the corresponding Google Docs will be deleted)

When syncing Google Drive to your computer, the following SHORTCUT files will be synced, which link to the online web-based Google Docs documents... .gdoc .gslides .gsheet .gdraw .gtable .gform ...
0
votes
0answers
13 views

How to getFullName()

I would Like a code example how I can get getFullName(), in Google Sheets. Basically, I have multiple users using Google Sheets and would want that every time they edit I cell in the Sheet, there ...
1
vote
0answers
13 views

Google Sheets' IMPORTXML pulling from the wrong region

I'm using IMPORTXML to get the price of an electronics component from Mouser: If the prices are refreshed as a result of me editing the spreadsheet, it requests from my computer, and results in the ...
0
votes
1answer
43 views

Split Master Sheet into Multiple sheets

I have a master sheet where the data is like this: Would it be possible to split this into multiple spreadsheets so it can look like: and the sheets are divided into Weeks? Scripts or formulae are ...
0
votes
0answers
39 views

How to move row in Google Sheets after checking a check box?

I'd like to be able to move an entire row from one sheet to another after a check box is checked. In this spreadsheet I'd like to be able to check the "Letter Sent to Client" checkbox and have the ...
1
vote
1answer
31 views

Escape single quote in cell content in a QUERY

My Problem I have a Google Spreadsheet with two tabs - one containing regexes and values: And the other containing phrases which matches regex in the first tab, and a QUERY Formula that picks the ...
0
votes
0answers
11 views

how to visualize time range data in google sheets

i have several thousand rows of data. Each row represents a task and includes the start time, end time, and data related to the task in the other columns. A task duration varies between 1 second to ...
0
votes
0answers
24 views

Function GetRange not found in Google App Script

function importCSVFromGoogleDrive() { var file = DriveApp.getFilesByName("data.csv").next(); var csvData = Utilities.parseCsv(file.getBlob().getDataAsString()); var sheet = SpreadsheetApp....
0
votes
1answer
11 views

Sliding window average of last N non-empty non-blank cells in Google Sheets

Similar to this question: Average of last 7 non-empty non-blank cells in Google Sheets I'd like a formula to compute a sliding window average of the last N non-blank cells in a column. e.g. For N = ...
0
votes
1answer
16 views

Assigning a value to a name from a list

I have items that I'm selling, and I've named these codes with the item I'm selling. I want to give these codes the price they should display. I've already got a formula to show the product of the ...
0
votes
0answers
25 views

IMPORTFEED Convert date format

I have a Google sheet that imports an RSS feed using IMPORTFEED and also the date of each entry using "items created" =IMPORTFEED("url", "items created") The date is shown as Sat, 13 Jul 2019 03:17:...
1
vote
2answers
23 views

Google Sheets Checkbox vs. Tickbox

Sometimes, I've seen Checkbox but I've also seen Tick Box. Does anyone know what's going on?