Questions tagged [google-sheets]

For questions about Google's web application to create and edit spreadsheets on a web browser or app. 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 chance that you get a satisfactory answer.

Filter by
Sorted by
Tagged with
0
votes
1answer
12 views

Limit to only include data from latest two entries

Question is related to this Google Sheet: https://docs.google.com/spreadsheets/d/1U13dvWPk8-hMybmhE1RKZ66y7RXXGPWt91_BNFWIup4/edit?usp=sharing How can I limit column A and B to only include Formdata ...
0
votes
1answer
12 views

Google Spreadsheets SUMIF/SUMIFS according to a another column?

I have two columns: Billing date : date Billing amount : currency I have another static cell where I want to sum all the Billing amount values, but only if the date in Billing date is greater or ...
1
vote
2answers
26 views

How do I count the number of times string “A” occurs in cell B1

Given the string "AABACCCDAC", how do I count the number of times "C" occurs? My initial thought was to use countif on a SPLIT of the string. But Split won't split on "" ...
2
votes
2answers
48 views

Calculate Total DateDif Average Between 2 Columns (start, end) according to year expressed in days, hours, minutes

I'm having trouble getting a formula to work to calculate the total average time between times (start and end). Sheet: https://docs.google.com/spreadsheets/d/...
0
votes
1answer
33 views

Google Sheets, formula changes when I insert rows

Why does my formula change, when I insert new rows into the source sheet (Formdata)? =sort(Formdata!B$2:B,1,TRUE) The first row in Formdata is a header. From time to time I need to empty the Formdata ...
0
votes
1answer
42 views

Google spreadsheets: Possible to enter in one of any of any given time and have others adjust accordingly?

Given five times, Pacific, Mountain, Central, Eastern and UTC... I want to enter a time in any one of those, not the same one every time, and have the others adjust. For instance, if entering "2:...
0
votes
1answer
19 views

How to sort mutliple columns by data in one?

I have two columns A and B, A has checkboxes that when set to TRUE it fills the adjacent cell in B green and strikes the text through. I want to create a filter that sorts columns A and B by the data ...
0
votes
0answers
12 views

Get sum of values of a single cell accross many sheets [duplicate]

I have 10 sheets from the same template with True/False values. I want to count the number of True values in a certain cell across all sheets. I tried the following but it doesn't work. =COUNTIf(['1'!...
0
votes
0answers
32 views

Can I exclude the bottom row from a Filter in Google Sheets?

I have a spreadsheet in Google Sheets, there is a header and 13 rows containing data. There is then a row containing the average of the cells above. I'd like to be able to sort the various columns in ...
0
votes
1answer
12 views

Linking an Add-On to a Drawing

This must be an odd question...I've searched but haven't seen anyone else ask. It probably seems a little unnecessary, but I'm trying to assign an Add-On (not a script) to a drawing/button that would ...
2
votes
0answers
14 views

Customize point style in scatter chart [Google Sheets]

Is there a way to add new point shapes (import new symbols) in Google Sheets charts? Also, can I customize there color fills, or remove the infill color?
0
votes
1answer
40 views

How to use an expression or formula on values from IMPORTRANGE

I am trying to use an expression on the values that are imported from another spreadsheet. The IMPORTRANGE copies the raw data, and with QUERY I can then specify which columns to use, so I can skip ...
0
votes
1answer
29 views

Average of cell range from string

In Google Sheets I have a string like this: "G4;G15;G26;G37;G48". How do I get the average of the values in those cells? I'm sure there's some arrayformula and indirect to be used, but I can'...
1
vote
1answer
40 views

Average the last N values in a Sheets column using Arrayformula

Experts, I need your help... I'm looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn't. Let me ...
0
votes
1answer
36 views

Help me separate each student into separate rows without inputting the parent data each time [duplicate]

I am collected data about parents and students. I need there to be a separate row for each student without entering the family data each time. I have always used the "pre-filled link" ...
1
vote
1answer
40 views

Automatically Merge Group of Cells Below a Text Value

I'm building out a scheduling assistant; the look is great, but it is very tedious to Merge & highlight each schedule block individually, so naturally I turned to Apps Script. I'm entering ...
1
vote
1answer
36 views

Using REGEXMATCH with list input

I'm trying to see if a COL A contains anything from a specified list. (if it does, I put say a '1' in COL B, or '0' if not. I'm currently doing it this slow, insane way. (putting this formula into col ...
0
votes
1answer
18 views

Wildcard for any value including blank

According to COUNTIF the documentation, criterion can contain wildcards including ? to match any single character or * to match zero or more contiguous characters. But this is incorrect because * ...
0
votes
0answers
22 views

Go into “Display” tab, click a name off the drop down list, and view that individual's data

I have 3 tabs. "Timestamps" "Techs/Locations" and "Display." My form timestamps when an employee clocks in or out. I have created two separate columns to carry over the ...
2
votes
1answer
45 views

Is there a way to copy a selection spanning multiple rows, and paste them as merged cells spanning two rows each, in Google Sheets?

I'm going to be honest; my biggest issue is describing what I wish to accomplish. I can't find the right word for it, so the title might not make a lot of sense. But the pictures should be clear, I ...
0
votes
2answers
25 views

How to link to source cell from filter output

I have a simple query that outputs columns A and B of a project dataset, when the value in column A is not empty: =QUERY(Projects!A2:B, "SELECT A,B WHERE A<>''") This creates an easy ...
0
votes
1answer
26 views

How to copy an image from one spreadsheet to another in Google Sheets

I have added an image into a cell of a Google Sheet, I'm trying to copy-paste it in the cell of a new spreadsheet but doing CTRL+C / CTRL+V doesn't work. I've added the image doing: Insert > Image &...
0
votes
1answer
15 views

Google sheet - highlihghting cell if in the same row other cell not empty

I'm trying to make a worksheet where I log shift hours. I made columns with dates and to each date a row of hours I want the date text to be green if in the same row a cell is not empty.. The code I ...
0
votes
1answer
24 views

Query or filter the data when using date time format

Example sheet. Sheet2 In cell A2 I have: =QUERY(Sheet1!A1:Q, "Select * where B >= datetime '"&TEXT($A$1,"MM-DD-YYYY HH:mm:ss")&"'") In cell A1 I have: 6/17/...
2
votes
2answers
19 views

How to make a Google Sheets cell output any string?

I can type 1+1 in a cell, and then it appears unchanged. However typing typing +1 or =1, it's not being taken as text, due to the prefixes, and it's shown differently. What to type so a cell has shows ...
1
vote
1answer
15 views

importing from multiple workbooks for Google Sheets to ignore workbooks that have nothing listed, instead of returning an error

={FILTER(IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "Trade and Manufacture!P17:T"), IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "...
0
votes
1answer
28 views

Google Sheets for an Automated Shift Bid Process

Hello I am looking for some help because I don't know how to go about this and have been unable to find any resources that guide me on doing what I am trying to do. So I work for an organization that ...
1
vote
1answer
34 views

Multipliers In Google Sheets

I'm trying to apply multipliers to rows within a column total, having issues.. I've tried multiple attempts using ARRAYFORMULA, MULTIPLY, SUM, and combinations of these, with no luck.. Search attempts ...
0
votes
0answers
10 views

Google Sheets chart only shows first ~250 rows?

I have a stacked area chart with an x-axis from B8:B350 and two series from C8:C350 and D8:D350 (so data range B8:D350) but the data only displays the first ~250 rows which is September to May. Any ...
0
votes
1answer
28 views

How do I convert rows with 45 columns into 15 separate rows, each with 3 columns?

I am working with a google sheet that gets data from an email parser. Each time an email comes in, a single row is created, and fills in these columns: B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, ...
1
vote
1answer
41 views

Creating Hyperlinks of Google Drive Items in Alphabetic Order

The original post that I got the google script editor code from was here: Get share link of multiple files in Google Drive to put in spreadsheet It works perfectly fine, but what I want to do is show ...
0
votes
0answers
13 views

What's the equivalent of Windows F4 to make a cell reference absolute (e.g. $A1$1) in Google sheets?

What's the equivalent of Windows F4 to make a cell reference absolute (e.g. $A1$1) in Google sheets? Online searchers tell me it's apple (command) + T but this just opens up another tab in Chrome. I ...
1
vote
0answers
40 views

How to make this Google Sheets script dynamic?

I'm working in a script to paste a table on the same sheet, in my code I'm only pasting the table on the row A18 function NewQuote() { var ss = SpreadsheetApp.getActive(); ss.getRange("A18&...
0
votes
1answer
33 views

How to run a different script on multiple Google Sheet tabs?

The code writes an auto email once a trigger is met in the sheet. There are 3 different tabs pulled from the main sheet and each tab should run the script but with a different message for each tab. ...
-2
votes
1answer
20 views

Google Sheets: How can I query all names and display it in another sheet according to it's category?

So I have an employee list and a checkbox according to their skills. How can I get the employee list and display them to another sheet?
2
votes
1answer
28 views

Howto sync offline sheet from Sheets app

I did some offline work on a google sheet file on my mobile using Sheets app. I actually did not realize that I am offline, as I never set this option, at least not on purpose. After I connected to ...
1
vote
1answer
58 views

Trying to fetch data from www.csgoempire.com with ImportXML function

I am trying to fetch data from this link using Google Sheets' ImportXML function. This is a betting site, and the link displays a list of roulette roll IDs on 26 June 2020. I am trying to fetch that ...
1
vote
1answer
50 views

Google Sheets formula to find rows with matching values, looking up in multiple columns

What would be the Google Sheets formula to search for a matching value in a range that goes across multiple rows an columns? For example I need to search the entire range H:P (all rows and columns) ...
1
vote
1answer
18 views

Matrix multiplication-esque concatenation?

I'm making a simple recipe maker in google docs, using row vectors and sumproduct I can get it to calculate the protein and calories for a given recipe. The way it's set up it can be hard to read the ...
1
vote
1answer
19 views

Regular Expression to wrap phrases between commas with carets (^)

I have a Google Sheet with example information like this Education, Conservation, Health, Human rights, Rural development, Poverty alleviation, Water and sanitation. -------------------- Human rights, ...
0
votes
1answer
16 views

How to best put many images on Google Sheets?

I need to put a lot of images on my Google Sheet but that makes the file so big. What I want is to put a link or something which can redirect us to another window which contains all the images.
1
vote
1answer
23 views

Referencing a cell from another sheet using a value in another cell [duplicate]

I'm trying to find a way to reference a cell from another sheet using a value in another cell from the "home sheet." I've tried to use INDIRECT but I'm not quite sure how to use it when the ...
0
votes
1answer
61 views

How to get a numerical value of a date with Google Apps Script?

I have a value 0.000347222222222222 in a cell, which when formatted as time, looks like 30/12/1899 00:00:30 . How to get it's numerical value with Google Apps Script? sheet.getRange('A1').getValue() ...
0
votes
2answers
37 views

Allow users to make yes/no decisions using checkboxes

I'm looking for a way my users can interact with my scripts. I'd like to use checkboxes to work like a "yes/no" command, where the script does "A" if the checkbox is ticked, and &...
1
vote
1answer
39 views

Isolating conditional formatting to each range in a column

I've set up conditional formatting to highlight the top 3 values (gold, silver, bronze) in a range of rows. However, I want to be able to copy/paste the conditional formatting below the initial rows ...
0
votes
0answers
21 views

Automatically Increase Document Number/Cell Number on open

I would like to be able to click on something which will open a new google sheet based on a template. I would need cell A1 to contain a number which increases by 1 every time the template is opened. ...
0
votes
1answer
18 views

Macro to move down and separate data

I wish to use a macro in Google Sheets to find the number 1 in column A then move that corresponding row and all following rows down to start at A9 row then find the number 2 in column A then move ...
0
votes
0answers
26 views

IMPORTHTML waiting on data [duplicate]

I am fairly new with query language, but I have been looking around the internet to find a solution to this problem without any luck. I'm trying to import data from leaderboards on specific tracks in ...
0
votes
1answer
24 views

How to Jump to Column Based off of Cell Value

My expected result is very simple - I have used data validation to place a list of column titles in a cell. When a user selects a column title and clicks the button, I want them to be taken to the ...
0
votes
0answers
32 views

How do I make filters auto-refresh when cells are updated via the API?

I have the following function running in Apps Script, however the onEdit function is only called via manual edits. It does not work when I edit the sheet using the API. Here is the function: /** * ...