A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

0
votes
2answers
36 views

Google Sheets, Conditional Formatting, Negative Numbers

What I'm doing in Google Sheets I have cell A1 with a value 10. I have cell A2 with value -11 and cell A3 with value 11 . I want to conditionally format cells so that when their value is less than -$...
-2
votes
0answers
5 views

Apply formula to a numeric part of cell value [duplicate]

In a Google Spreadsheet, I have a column of task IDs, with values like T1, T2, T13 (i.e. all values starting with the same text). I would like to use a formula on the values of these cells (for ...
-1
votes
1answer
49 views

Getting e-mail adresses using a filter in Google Sheets /w scripts

I am trying to extra email replies from gmail. I already found some code that is supposed to work. My question is: how can one expand it to support a filter as a variable? (in this case so a filter ...
3
votes
2answers
44 views

Data Validation: how to ensure all columns have unique data within the column

I finally found this answer which tells how to use data validation to make sure all the values in one column are unique. But it doesn't tell if there is a way to write a single validation rule that ...
0
votes
0answers
30 views

Is there such a thing as a “git blame” for Google sheets

I know that one can easily see the revision history of a Google Sheet. But then that's the same as running git log.. It shows shows a chronological order of changes to the file. I'm actually ...
4
votes
2answers
54 views

Count occurrences of names so that only one in each row counts

I have a set of data in one Sheet tab, and a counter on another tab in the same document. I want the counter (in sheet 2) to read through the text in the table in sheet 1, and update how many times ...
1
vote
1answer
16 views

Filter numbers in one column based on whether they are higher than in another column

How to filter numerical data in column B based on whether or not the number is higher than the number in column A?
0
votes
2answers
30 views

Track highest number in a cell over a series of recalculations

How do I keep track of the highest number that was in a cell in Google Sheets? I'm running random numbers through a cell and I would like to track the highest number reached. Specifically, I am ...
0
votes
0answers
24 views

Create dynamic form with data from a spreadsheet

I'm trying to create a form that collects the questions from a spreadsheet dynamically. Example Table: Name Number Age Don yes no Andy no yes Marry yes yes The ...
2
votes
1answer
32 views

Is there a way to get the summary section from a form into a Google Doc

I've got a survey using Google Forms and it has useful graphs in it's summary section (under responses). How can I get these graphs into a Google Doc? I tried downloading the CSV file and recreating ...
2
votes
2answers
38 views

Run onEdit script in one sheet only

I have this Script so far, function onEdit() { var s = SpreadsheetApp.getActiveSheet(); var r = s.getActiveCell(); if( r.getColumn() != 2 ) { //checks the column var row = r.getRow(); ...
0
votes
1answer
37 views

Is there a script to draw a list of variables from a table to Google Forms?

I would like to have a field in Google Forms that allows the user to select a value from a "List" of values. The options in the list being drawn from a list in the sheet itself. This allows me to add ...
0
votes
2answers
19 views

Grouping Multiple Data to Output Common Values in Google Sheets

Apologies if this has been covered before... I'm creating a Google Sheet spreadsheet to group the amount of occurrences of a particular product, based on the country that it is assigned to. Imagine ...
-2
votes
0answers
13 views

Automate calculations when adding new services [closed]

With the little knowledge I have with spreadsheets and with lots of help from Google, I've managed to create this little project management spreadsheet to help me tracking stuff and it works pretty ...
0
votes
0answers
21 views

How to reference rectangular range using IMPORTRANGE in Google Sheets

sandwich answered this question. How can I use their solution: =IMPORTRANGE("KEY"; "Sheet1!G"&B7) to import a rectangular range? I've tried =IMPORTRANGE("KEY"; "Sheet1!G"&B7":H"&B8) to ...
0
votes
0answers
21 views

Google Docs - Sheets (COUNTA also includes cells with formula)

My test sheet: https://docs.google.com/spreadsheets/d/1g3J7fumAak3ffWTiCa3vjM32zf5xEqkeuOlyDM3tffM/pubhtml I assign asset tags to a store under the "Asset Tags" worksheet. I find the asset tag that ...
0
votes
1answer
30 views

How do I add a field in Google Forms to show the sum of multiple fields (that are numbers)

Say for instance I want the user to input into 4 fields the meters they will dig up in different areas. On the form itself I would like to have a number at the bottom displaying the sum of 3 of said ...
0
votes
1answer
130 views

How to turn off Insert/Overwrite in Google Spreadsheets

When editing text in Google Spreadsheets, in the formula bar, or editing the name of the sheet, overwrite is toggled, and pressing the insert key (numberpad or 'regular' insert key, numlock active or ...
2
votes
1answer
29 views

How to use Google Spreadsheets to chart completed tasks using time series graph?

I have the following columns in Google spreadsheet Date and Completed Tasks Sample data includes May 17, 2016 3 May 18, 2016 10 May 19, 2016 9 May 20, 2016 8 May 21, 2016 9 I ...
1
vote
1answer
13 views

How to add columns if a neighbor does not exist in google sheets

I have a sheet that looks like this: estimate | actual | name 10 | 1 | Bob 20 | 9 | Sue 30 | | Bob 40 | 3 | Sue How can I get a sum of where the ...
-3
votes
1answer
30 views

Use regexmatch to determine if one cell's content contains another's

I want use a cell in regexmatch as regular_expression parameter. My goal is to draw green background for A1 if B1 contains A1 inside of it. How to do this with regexmatch?
1
vote
2answers
28 views

Combine text from multiple cells into a single cell with multiple mixed delimiters

Does anyone know a formula to combine several cells into a text in one cell with mixed delimiters between words? Cell A1 - First Name Cell B1 - Last Name Cell C1 - Job Title Cell D1 - Company I ...
0
votes
0answers
11 views

Google Sheets Autosort without using additional sheets [duplicate]

I am looking to create a dynamic task list on Google Sheets, such that the columns are sorted alphabetically (I have put A: B: etc in front of the things I want to sort so that it can work) ...
0
votes
3answers
86 views

How do I compare the values of two columns and return the result in new columns?

EX. https://docs.google.com/spreadsheets/d/1CwFXSwIgnPdMocbrhTFZbEPRyXZUiovvWNy9MtOagxE/edit#gid=0 I wanted to compare two column "fix version" and "Affected version" and return the result bases on ...
0
votes
1answer
69 views

Query to Import HTML / XML Data Into Google Spreadsheets

I'm using Google Spreadsheets to retrieve a data table ('id=idp6855942304') from this site. The full sets of tables can be viewed by clicking on "All Reports" from the menus on the left side of the ...
2
votes
1answer
48 views

How to find URL of the current sheet

In Google Sheets, how do I create a hyperlink to this sheet? I have a sheet which I'm about to download as PDF for archive. It would be useful to include a link in the sheet, pointing to itself, so ...
2
votes
1answer
28 views

Can I protect rows in a Google Docs spreadsheet from sorting?

I can sort a Google Docs spreadsheet on any column, but that sorts ALL of the rows. I don't want to sort the rows that contain the column labels. Is there a way to protect them from being sorted? I ...
0
votes
2answers
19 views

Three nested IFs in CONCAT throwing error

So I have this formula: =CONCAT(=IF(F26 = " Kb","0",IF(F26 = "16 Kb","16",IF(F26 = "64 Kb","64")))) I'm trying to return the result "0" or "16" or "64". Three possible inputs, and three outputs. ...
1
vote
2answers
59 views

How to Determine the Xpath Query to a Datapoint in a Table for Use with IMPORTXML?

I'm looking to retrieve the most recent financial figure for a single data point from a table using IMPORTXML. The target page displays three sets of financial figures for a company on quarterly and ...
2
votes
1answer
52 views

Google Forms - Show unique ID on the form (before submitting)

I need an anonymous form (no names, emails etc) that shows unique ID so that if a person decided to reveal that ID to me I will be able to find his/her answers in the spreadsheet. The ID can show on ...
1
vote
1answer
13 views

How to use user-defined functions for custom formatting?

I’m writing a simple Google spreadsheet to list every account number which could come in handy to our non-profit in the future. Of course, one can expect some typo will get in some point in the future....
1
vote
1answer
29 views

If a string is contained in a row return the value of the first cell of the row

I've this Google Spreadsheet: https://docs.google.com/spreadsheets/d/1-QV49oKCf58HgLBgC8gJ3gc6IXJZAo2xvh6vPTjY05o/edit?usp=sharing check the row in Sheet1 where the domain in Sheet2 is contained ...
2
votes
1answer
15 views

Autoincrement a date in a formula when dragging horizontally

I have a date in column A, and a formula in column B that references the date. I'm trying to drag the formula over to 6 more cells horizontally, representing the 7 days of the week, Column A being ...
1
vote
1answer
38 views

How do I copy data if cell contains text?

Basically, I have a book which gets data anytime someone fills a form. There are five groups I need the data divided into. If a cell contains one of the five answers all the answers by user need to ...
1
vote
2answers
41 views

JavaScript To Dynamically & Daily Select The Next Worksheet In An Open Google Sheets File

At work using Chrome, I have a Google Sheets file saved on my Google Drive folder that's shared with me with edit privileges that I keep open on my MacBook Pro. After I've originally opened the file ...
3
votes
2answers
34 views

Conditional format for column headers where all cells are numbers

I'm trying to color the header if all of the cells in that column are numbers. Blank or non-numbers should break this. I have: custom formula: =ISNUMBER(A3:A) But it styles the cells instead. Not ...
2
votes
2answers
35 views

Organize employee time log into a table with a row for each employee

I want to get all the Timestamp of an employee (EmpID) from one sheet to another using Google apps script or otherwise. Explanation In Google Sheets, Sheet1 has the following items in 3 columns | ...
0
votes
0answers
20 views

Conditional format — copy format of another cell (Google Sheets)

I'm using Google Sheets spreadsheet, hope it's ok to ask about it here (in superuser). Chances are the methods to accomplish this might be different from Excel or LibreOffice, if it can be done at all....
6
votes
1answer
78 views

How to open a Google Sheets file and go immediately to the end of the file

I have a Google Spreadsheet with 100 lines. Every time I open that file, I want to insert a row on the end of my table. How can I jump to the last line of the table when I open the doc? Because ...
0
votes
0answers
7 views

Google sheets: Automatically including new rows on separate sheet

I have a google sheet document with two separate sheets, Sheet1 and Sheet2. I want column A on both sheets to stay the same. Column A of S2 simply has ='Sheet1'!A2 for cells A2 on down. Let's call ...
2
votes
1answer
33 views

Different bar types based on column value

Instead of having one column for each new "type" below, is there any way to achieve the same result using the non highlighted cells in the image below? To elaborate, I would like different bar types ...
0
votes
0answers
12 views

Add days of the month to a chart [duplicate]

I have 2 columns, a boolean value (nok/ok) and a date. I am tracking in which particular date I have completed a study module(s). When I add a chart to show the progress through time, the chart shows ...
1
vote
0answers
16 views

Pasting multiple formulas at once

I have a column of formulas (each created by concatenating several parts of a formula in cells to the left of it on each row), I want to be able to copy and paste these into a new sheet, whilst ...
0
votes
0answers
11 views

How to format cell if date is 7 days past the date listed in cell [duplicate]

I'm trying to keep track of regular due dates based on the last time the task was completed. Example: I have a task that needs to be completed about once every month. In column B I'm inputting the ...
0
votes
0answers
26 views

One form submit produces two updates to spreadsheet

I have an application in which a user submits a form, the data are logged in a spreadsheet, the form submit triggers a script that records the new data and deletes any previous form responses from ...
1
vote
1answer
29 views

Combining columns horizontally with an arrayformula

I need to join columns with a formula (not a script) in Google Sheets in a different way than others I've found--here is my original data, supposing that the Avengers train by playing racquetball: ...
3
votes
1answer
112 views

Using Google Spreasheet's IMPORTXML Function to Download Selected Multiple Columns

I use Excel's XML mapping to download RSS feeds and select elements to display in column. I'm currently using Google Spreadsheet's IMPORTXML functions and trying to recreate my below Excel results. ...
1
vote
1answer
51 views

Hide row if cell contains specific text

I am trying to hide a row when a cell is filled with "x". I have tried using scripts but they don't work. So there are "A", "B" and "C" filled in column F. I want the script to hide rows where "A" ...
0
votes
1answer
19 views

Hide a row of cell when a specific cell is filled

I am trying to hide a row of cell when a specific cell is filled in on Google Sheets For example: On row 2 cell E2 is filled in with "1", I want to be able to hide the entire second row. Is there ...
0
votes
0answers
11 views

Google Spreadsheets: add hours to date problem

I've encountered some kind of strange problem with Google spreadsheets. I've 2 cells, in one I've date, lets say its 10/10/2010, in 2nd one is hour, 12:00:00. I get those from input cells, but when I ...