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
3
votes
2answers
42 views

Conditionally formatting a column based on content of nearby column

I am trying to get cells in column C to turn green when the cell in the same row of column B has a value in it. I think there is a bug because the custom formula below is not working.
1
vote
1answer
43 views

Writing a macro to create a new tab and name that tab based on a cell value in Google Sheets

I would like to have a Macro that generates a new tab and names that tab based on a cell value. There is a tab called 'Dashboard'! on which the user enters information in assigned cells. Afterwards, ...
1
vote
1answer
21 views

How to combine same value for Google sheet query?

I am trying to create a separate tab in my Google sheet where I can filter the Trainer to see which days they will be conducting a Day 1 and Day 2 Training. Day 1 Training: =iferror( query('...
2
votes
1answer
38 views

Dynamically reference data from multiple tabs and sort it

I've got a spreadsheet with multiple tabs of data. The data contains 2 columns, A = Categories, B = Quantity. I've created a copy of the spreadsheet HERE (please use file > copy) if you would like ...
0
votes
1answer
40 views

How to sort Column B to match with column A in Google Sheet?

I have 3 columns in Google Sheet. Column A have 95 rows with value. Column B and C have 500 rows with value: I need to sort Column B to match with Column A. Column C represent value associated with ...
1
vote
1answer
52 views

Filter the row containing an exact word and highest value in Google Sheets

I have a table (Master sheet) in Google Sheets that looks something like this: https://ethercalc.org/t8f0ze0kyx6a I need to filter out an entire row of information on a separate sheet (Last Lesson) ...
2
votes
1answer
26 views

Automatically copy first 3 columns from one sheet to each new sheet in Google Sheets

I'm having columns A, B and C filled with data in Sheet1. Those columns contains data that is static for each new sheet. Currently, I need to manually copy the first three columns to each new sheet I ...
0
votes
0answers
12 views

How do I set my sheet to go straight to a certain column when opening the document [duplicate]

I have dates going along the top of the document and would like it to go to 'todays' date when I open the document rather than scrolling across each time.
2
votes
1answer
22 views

How to sum the values of same vlookup results?

I have this formula it gets the single data from another file from a sheet at column 7. =ARRAYFORMULA( if( len($A2:$A); iferror( vlookup( ...
0
votes
0answers
30 views

I need help with calling new data from a Google Form sheet into multiple other sheets automatically

So, I'm setting up something for a friend of mine, and I've been trying to mess around in Google Sheets with getting something to work, but I've run into a bit of a roadblock. To put it simply, I want ...
0
votes
1answer
16 views

Importing answers from a Google Form into another Google Sheet, then adding a column for notes

I have been using a Google Spreadsheet with a master list created from a Google Form. I created a new tab to rearrange the order of the entries to the newest ones on top. However after those cells ...
2
votes
1answer
22 views

Using IF statements to apply an email address depending on teacher name

I am attempting to create a sign in sheet for students when they are sent to a "calming" room. The idea is that when they reach the room, they enter their name and select their teacher's name from a ...
0
votes
1answer
35 views

Summarise data in a pivot table, but exclude certain data if it has a certain value in a certain column

I've got some data in a Google Drive spreadsheet that I want to run a pivot table on. There are 3 columns Element, Cost, Omit as per screenshot below I want to summarise the cost by element, but ...
0
votes
0answers
10 views

Button that cycles through a list of values in Google Sheets [duplicate]

I want to see how different headlines look within a Google Ad, so I have set up an ad template in one tab and a list of headlines in another. I then want a button on the template that can cycle ...
0
votes
1answer
29 views

How to prevent “invoked too many times per second” error [duplicate]

The main requirement is to find travel time with traffic data between two locations or zip-code. Inputs parameters would be from Location, destination, arrival time(this is between 6AM 8AM), mode of ...
2
votes
1answer
33 views

Need unique values of a range in google sheets

I need to list unique values of 12 columns in a different sheet columns. Like sheet 1 has A column. I need to list the unique values of all 12 columns in Sheet 2 to Sheet1 A column. Like: want to ...
1
vote
1answer
33 views

Synchronizing Cell Changes Across Sheets

I'm trying to connect a pair of cells on different sheets within the same Google Spreadsheet, so that after a new value is entered into one cell, the other cell will update to match that value, going ...
1
vote
1answer
16 views

Fill-down formula to last used row

This function is to copy-down a formula C2424 to the last used row. Instead of copying it down to the last used formula its copying it to the last existent row within the sheet. How can I do it only ...
0
votes
1answer
13 views

Adding checkbox format in column

Trying to add checkbox format to a column using Google Apps Script to this working formula sales.getRange("I2").setFormula("=IF(COUNTIF(B:B,B2)=COUNTIFS(B:B,B2,E:E,TRUE),TRUE)"); var ...
0
votes
1answer
21 views

Cell Reference in a QUERY statement not working

I'm trying to add a cell reference (on the same sheet where the QUERY function is at) to a QUERY function On B1 there is this value: 13/09/2019 QUERY function: =QUERY(FORMULARIO!A1:S, "SELECT G ...
2
votes
1answer
68 views

How to make a bold sparkline in Google Sheets?

Is it possible to thicken the line graph shown when using the formula =SPARKLINE(L4:L)?
1
vote
1answer
28 views

If values in a range appear in another range, remove from list in another cell

I've been stumped on this googlesheets problem and can't think of the best way to do this or I'm overthinking this. Essentially if I have a list of values, I'd like to compare it to another list of ...
0
votes
1answer
47 views

Target different column

The following Apps Script function copies certain values from a row given from one sheet to another based on a value and also formats the output. It works perfectly well. It was written by @Tedinoz ...
0
votes
1answer
33 views

Copying a cell's background colour to other multiple cells via script

Update (01/10/2019); code is now: function copyFormatting() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var fromRangeGJ14 = sheet.getRange("GJ14"); var toRangeGJ14 = sheet....
1
vote
1answer
21 views

Import the whole table from nextgenstats.nfl.com [closed]

I am trying to import the whole table from this site into a Google Sheet, but I keep getting the imported content empty error. I realize this error is very common and I have done my fair share of ...
1
vote
1answer
28 views

Syntax of field “Google Sheets / Pivot table editor / Filters / Filter by condition / Custom formula is”

In Google Sheets, if I create a pivot table, there is a Pivot table editor I can use to configure the pivot table. The lowest section of the interface of the editor is Filters (which allows me to add ...
1
vote
1answer
54 views

How to sum values between two dates?

I have a table made up of numbers followed by dates like the one below only with more columns (link below). How would I sum only the values that occur in one specific year? For example, in this case,...
1
vote
1answer
36 views

Need help clearing specific cells in multiple tabs in Google Sheets Scripts

I'm having trouble editing this code to get it to edit multiple sheets at the same time. It works for whatever is active, but I'd like to disable that and have it work on each tab named after each ...
1
vote
1answer
21 views

Google Sheets conditional formatting with AND for value range

I'm trying to set a colour range of conditional formatting based on another cells value. So if A1 is larger than A2 it will be Blue, if A1 is between 0 and A2 it will be Yellow and if A1 is less than ...
1
vote
1answer
53 views

How to automatically fill missing data in Google Spreadsheet

I'm searching for the exact same thing as Google Spreadsheet: How to fill in missing values (inbetween filled values) automatically I made a spreadsheet for tracking weight, where each row is one day,...
2
votes
2answers
59 views

How to pull values from another sheet for dynamic Data validation

This one is far out of my league. To simplify things: Sheet1 B4 has a Data validation drop down list Ex. Vancouver, Surrey, Burnaby, etc B5 has a Data validation drop down list Ex. 500, 1000, 2000, ...
3
votes
1answer
44 views

Importing a row from one Google Sheet to another

Doc1: I have individual sheets setup for each Game Week which list the football matches being played on that particular week. Doc2: I have an individual sheet setup for each team. I'm looking to pull ...
0
votes
0answers
26 views

Google Form field ID

I want to link a Google Sheets sheet with a form and pre-populate some of the data from the sheet into the form using this formula: =HYPERLINK("form URL entry.ID="&A1&"&entry.ID,"Link") ...
2
votes
1answer
24 views

Changing a date in google sheets from MM/DD to DD/MM without changing the values

I have a survey data set where respondents DOB was entered as "DD/MM/YYYY". Sheets has formatted some of the cells as "MM/DD/YYYY", and where that wouldn't make sense because the day is greater than ...
2
votes
0answers
13 views

Disappearing hyperlink text in Google sheet [closed]

I have no idea why this issue is occurring, as I have previously had no issues adding links to text in this document before. I update it every year for a sports-tracking related hobby, and have never ...
0
votes
0answers
34 views

Lookup with two criteria

I want to check the Form Response Data based on two criteria. Here is the Form Response Data (on a Separate Tab): I want to fill the 3 Columns (on the Submissions Tab, image further down) with data ...
3
votes
1answer
71 views

How to automatically convert all Microsoft Office file attachments in Gmail to Google Docs?

My Gmail has thousands of emails. Each email will have several attachments as Microsoft Office files (.doc, .docx, .xls, .xlsx, .ppt, .pptx). Currently, I want to automatically convert all Microsoft ...
0
votes
1answer
21 views

How to give a column a green border if the second row's holds a date = TODAY()?

I'm trying to make a useful schedule sheet to keep track of all of my class assignments: I want to write a script in the script editor that says, for(Column A-Z){ if(A2,B2,etc = TODAY()){ ...
2
votes
1answer
23 views

How to sum amount with a criteria separately by day in Google Sheets?

I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month. The file for monthly account contains a couple of sheets - each ...
0
votes
1answer
20 views

Pushing data from one sheet to another based on a condition, without making sheets dependant on one another

Trying to see if something like this is possible within Google Sheets. I have 2 test sheets that are linked with query(importrange(... And I can have Sheet2 updated automatically when a row in sheet ...
0
votes
1answer
47 views

Use `setValue` to update a column based on values of two others that match their respective values in reference cells

I am new to Google AppScript and would really appreciate some help here. I want to use the setValue along with the for loop and if statement in Google App Script to scan multiple columns in Google ...
2
votes
3answers
33 views

Entering fractions verbatim in Google Sheets

I'm trying to put some marks in a spreadsheet and have a lot of unsimplified fractions or fractions like 19.5/20. Is there a way to enter fractions and have the cells be usable in other formulas, ...
1
vote
1answer
16 views

Hide RUN message in Google Sheets? [closed]

I have created a script on a Google Sheet. Is there a way to hide the balloon message "SCRIPT IS RUNNING" ?
0
votes
0answers
14 views

Auto-updating column in Google Sheet [duplicate]

Trying to figure out a way to have column next to modified column auto-update to show a timestamp of the last update. e.g. 1A is modified and in 1B auto-updated date, 2A modified and 2B auto-update ...
0
votes
3answers
40 views

How to use Importrange in a Query and filter by dates

I am trying to IMPORTRANGE if the condition in column1 that is text formated ( Column1 = September 4, 2019 alas 5:10 pm ) contains the word September. I am using the following formula. The other ...
0
votes
1answer
17 views

Highlight row if partial text matches in either of two columns

I have a sheet that has a list of job names, and then the last failure and success of each job. The dates are listed with duration words instead of dates, ie 1 mo 12 days or 3 days 6 hr. I want to ...
1
vote
0answers
47 views

Split rows by comma into multiple rows and divide between splits

I have a Google Sheets spreadsheet (sheet 1) which I use as source for a function to split a row into several rows in a results worksheet (sheet 2). The split function divides the row when it finds ...
0
votes
1answer
15 views

How to duplicate range of cells n-times(range can variate and must be based on column)

Can someone explain for me how with the help of formula I can solve this issue? We have an input range of Tags (letters and numbers). Whole massive must be duplicated in the same order n times. The ...
0
votes
0answers
23 views

Multiple filter Array formula in google sheets

I am trying to get my array formula to be able to filter on multiple drop down menus. I have 2 separate formulas that work but I cannot combine them. Is there a way to combine them to make the Array ...
3
votes
1answer
39 views

Filter and match multiple columns in Google Sheets

I have a list of teams and their corresponding owners in Google Sheets. (owners own multiple teams) I have a great formula that will filter the list by owner name: =FILTER(A2:A6, MATCH(B2:B6,"RINGO",...