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.
5,283
questions
0
votes
0answers
4 views
Output counted list with extra qualifier
I currently have a table with the following rows:
Comm ID Item Name Months Orichalcum Tempest Jade Green Iron Weltsilver Ambergelt Beggar's Lye Dragonbone Iridescent Gloaming ...
0
votes
1answer
5 views
How to work with Unicode characters in regexes in Google Sheets?
When using REGEXREPLACE with user submitted strings in Google Sheets, I have come across a special Unicode white-space character ([No-break space][1], hex 0xA0 or dec 160) that resisted my efforts to ...
0
votes
1answer
5 views
Google Sheets: Conditional Formatting Column C when Column B is not empty
I am trying to get column C to turn green when column B has a value in it.
1
vote
0answers
19 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
2answers
17 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
15 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
32 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
votes
0answers
7 views
Google App Script Execution Triggering Scenarios [on hold]
Does the Google Apps Script trigger to run after you make any changes to it on the console ? Because after I made several changes Service invoked too many times for one day: urlfetch. (line 133) in ...
1
vote
1answer
45 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) ...
1
vote
0answers
19 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
10 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
17 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
23 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 ...
-1
votes
0answers
9 views
google spreadsheet by using Distance Matrix API Pass the arrival time [on hold]
Hi I've used a simple script before to retrieve the following data set but now I need the urgent help from this community to pass the arrival date to this code.
To summarize the requirement:
I'm ...
0
votes
1answer
14 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
16 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
21 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
25 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
23 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
27 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
15 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
11 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
19 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
61 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
25 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
43 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
19 views
Copying a cell's background colour to other multiple cells via script
Update; code is now:
function copyFormatting() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var fromRangeGI14 = sheet.getRange("GI14");
var toRangeGI14 = sheet.getRangeList(['N9','...
0
votes
0answers
22 views
How do I apply two conditional formatting rules to a single cell?
I have two groups of rules... one color-codes the fill of cells based on one condition for visual grouping of data (7 rules for 7 groups); the other color-codes the text of cells based on another ...
1
vote
1answer
17 views
Import the whole table from nextgenstats.nfl.com [on hold]
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
11 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 ...
0
votes
0answers
56 views
Define Origin, Waypoint, Destination [closed]
I have a google spreadsheet with data as below.
emp_id Date Point
Emp-107 1-Aug-19 Origin
Emp-107 1-Aug-19 Destination
Emp-107 2-Aug-19 Origin
Emp-107 3-Aug-19 Origin
Emp-109 ...
1
vote
1answer
53 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
35 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
33 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
46 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
39 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
23 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")
...
-1
votes
0answers
61 views
Copy row to another sheet based on value and divide comma sepparated values into multiple rows [closed]
Example sheet here
What I want is a Google Appscript that would automatically copy CONTACTS! row to SALES! last row when CONTACTS! row has "SOLD" value in column J **
THe thing is it also needs to ...
2
votes
1answer
21 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
11 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
30 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 ...
2
votes
0answers
48 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
18 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
20 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 ...
-1
votes
0answers
20 views
Google Sheets feature to view multiple different columns and allow editing?
I am working with a spreadsheet used by multiple people that contains more than 20 columns. Part of my job process is to go through the rows and look at data from 3 or 4 different columns that aren't ...
0
votes
1answer
18 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
36 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
27 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, ...
