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

learn more… | top users | synonyms (2)

1
vote
2answers
39 views

How can I highlight duplicate cells?

I'd like a formula that highlights all duplicate cells, even if they are in different columns. In my example spreadsheet A1 is equal to D15, the formula should highlight both, and so on with all ...
1
vote
2answers
48 views

Sorting Pre-Sorted Google Form Data by Timestamp

I have Google Form checkbox data that I am separating out into another table so that each checkbox selection generates its own line. I received some help in pulling the data over, and the resulting ...
1
vote
1answer
34 views

Conditional formatting based on multiple cells

I'm trying to figure out how to write a formula for Google Spreadsheet where: If F3 Contains Data and G3 is more than 0 then cell J3 is Highlighted in Green Can anyone help me out with this?
1
vote
2answers
52 views

Formula result depending on the week of the day

I've got a bunch of cells that have formulas to display the day of the week according to the date above it. (I'm using Google Sheets by the way) I want the cell underneath the day of the week that ...
1
vote
1answer
120 views

Sorting alphabetically (A-Z) ignoring beginning “A”, “An” and “The” using a script

I have a Movie list using Google Sheets that I would like do some sorting with using a script rather than a formula. Column A is the movie Title. I would like to be able to sort the movie titles ...
1
vote
1answer
30 views

Get unique rows and their count in Spreadsheet

I have a sheet as below, | A | B | C | ------------- |abc|123|Yes| |jkl|123|No | |abc|123|Yes| |abc|567|Yes| I want to get the output like, | A | B | C |Count| ------------------- |abc|123|Yes| 2 ...
1
vote
1answer
24 views

How do I change the year in Google spreadsheets

I'm copying and pasting things over from an old spreadsheet and the original dates where marked like '25/4' so dd/mm but I never included the year, well the problem is these where dated in 2014 but ...
1
vote
1answer
104 views

How to create an automatically updating Google spreadsheet with information from Steam?

How would I create an automatically updating Google spreadsheet containing information about Recent Activity in Steam? Table must look like here: But the hours column must automatically updating ...
1
vote
1answer
55 views

If time is before 17.00 use 17.00 OR if time is after 17.00 use value in cell

Headline says it all. I'm trying to calculate worktime in minutes. Ex: https://docs.google.com/spreadsheets/d/1GmGyi4kQf8I2qvEJjfG3OZvm_n_kBbsw_doTi9J0KCk/edit?usp=sharing I have to state when ...
1
vote
2answers
30 views

Show number in a cell - based on a value from another cell

I need help with the question on the picture. Can anyone make a formula for me? Thanks
1
vote
1answer
336 views

Linking a URL to text using google script in spreadsheet

I have a URL ready to be followed. I am sending an email using Google script, and in this email I need this link to be there... but I do not want it to be: ...
1
vote
1answer
138 views

Google Sheets - Auto update table and auto add new data entries

I have a table in wich raw data is entered manually, with info about clients and the requests they made. And then I have this table in which I'm trying to analyze a bit the raw data (# of requests ...
1
vote
1answer
227 views

Can I use ArrayFormula with CountBlank or IsBlank if so how?

=IF( OR(COUNTBLANK($F3)>0, COUNTBLANK($G3)>0, COUNTBLANK($H3)>0, COUNTBLANK($I3)>0), "Missing Values", $E3+(index(LSI_Index!$B$4:$B$40,MATCH(VLOOKUP($G3, ...
1
vote
1answer
2k views

How to skip blank cells in Google Spreadsheet/Excel when copying a range from source to destination by Formula

I have three levels of hierarchical data in Google Spreadsheet that looks something like this: As data is being entered in here, I want it collect in three separate sheets like this: And The ...
1
vote
3answers
5k views

How can I replace blank cells with zeros in Google Spreadsheet?

I have a spreadsheet where someone else entered the data. They left blank cells instead of recording 0 values. How can I replace the blank cells with 0s? I tried find and replace with regular ...
1
vote
1answer
2k views

Filter columns in Google Spreadsheets based on fill colour

I've got a long list of items that I need to check. I use a traffic light system to track where I change the cell colour of the cells in the progress column: red = haven't done yellow = in progress ...
1
vote
2answers
200 views

Calculate time values in a single Google Spreadsheet cell

Is there a way to add two times, or durations, in one line in a Google Spreadsheet? Something like: =1:14:25+0:17:12 I know that I can use the time command in-line, and that I can add times from ...
1
vote
1answer
444 views

Menu option Insert -> Script… is missing in my Spreadsheet, how do I fix it? [duplicate]

I've 2 documents, in 1st one I was able to add 'Script...' (as suggested here) from the Insert menu, but on the 2nd one and all new which I'm creating I don't have this option present. Why this ...
1
vote
1answer
438 views

How do I import text file as-is to Google spreadsheet?

I'm importing a text file with some columns being 000000, 0000, 000000000. Google Docs converts them all to 0 - treating them as numbers. How do I make the columns contains exactly the string that was ...
1
vote
1answer
54 views

Add a sheet from a template

Is there a way to add a new sheet to a spreadsheet from a template? If I'm using a template for time, I'd like to add a new sheet from the same template every two weeks.
1
vote
2answers
729 views

How can I easily fill a column in Google Spreadsheet with random values from a list?

I need to create some data for a simulation. So I wanted to take a spreadsheet, and for each column, fill it with random data falling within a domain. A uniform distribution is good enough. For a ...
1
vote
1answer
3k views

How to paste a comma separated string over multiple cells in Google Spreadsheets

Say I have a string of: l,f,x,a,s,f I would want to paste this every comma separated value over 6 adjacent cells. How would I do it?
1
vote
1answer
119 views

Using SUM() and VALUE()

I have a spreadsheet with invoice amounts. Each amount is a link to the given invoice. Can I use sum() and value() (or any other functions) to somehow use the displayed value for sum() (and not the ...
1
vote
2answers
318 views

Google Spreadsheets: Add 1 if there is data in cell

I have a spreadsheet of data which lists the inspection results of schools, in which I have a column that states the result or is empty if that particular school has not yet been inspected. I want ...
1
vote
1answer
406 views

Sum every 9th row starting from the 4th row in Google spreadsheet

I have a column where there is a number every 9th row. The problem is that my first value starts on 4th row then all the next ones every 9th row. So far I tried this: ...
1
vote
2answers
51 views

Is there a more efficient formula to use than just copying and pasting?

D3 =(B3^3.0)-(B2^3.0) D4 =(B4^3.0)-(B3^3.0) D5 =(B5^3.0)-(B4^3.0) D6 =(B6^3.0)-(B5^3.0) D7 =(B7^3.0)-(B6^3.0) .... I rarely used Excel but since I don't have it installed on my computer I'm using ...
1
vote
1answer
2k views

In Google Script, get the spreadsheet cell calling a custom function

In VBA for Excel, I can get the column of the cell making the call to a custom function like this: ThisCol = Application.Caller.Column What is the equivalent in Google Scripts? I've been trying to ...
1
vote
2answers
109 views

What is the best approach to adding additional costs to items in Google Spreadsheets?

In Google Drive I'm keeping a spreadsheet of everything I spend money on. I have a heading for the cost but alone that's insufficient. For example, if I tip after a meal I really want to have that ...
1
vote
2answers
578 views

display google docs spreadsheet in mobile mode in desktop browser

When viewing a google docs spreadsheet in a mobile browser you have handy filter buttons at the top. How do I display mobile version of the page in a desktop browser? Preferrably Firefox. Edit: I seek ...
1
vote
2answers
266 views

Finding and removing periods in Google Spreadsheet

I'm using a Google Spreadsheet and trying to create a formula to remove a period and anything that comes before it in a cell. For example: idontwantthis.iwantthis I have used this in the past: ...
1
vote
1answer
6k views

Circular dependency detected even while using IFERROR

I have the following function in the cell B2: ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0))) And the following function in cell C2: ARRAYFORMULA(SUM(IFERROR(Purchases!C3:C, 0))) Basically, the ...
1
vote
3answers
1k views

Missing sort in Google Spreadsheets menus

I have a Google spreadsheet that has the A column as an ID field (incrementing numbers 1...1000; no formula, just numbers). That column is frozen as is the first row of headers. I sorted by column B ...
1
vote
1answer
12 views

How can I get the halfway date of two dates in Google Sheets?

I have two date columns in mm-yy format and I need to know the halfway date of these two. What formula do I use? I tried simple subtraction of the two dates and then divided it by two but it didn't ...
1
vote
2answers
40 views

Extract the link text and URL from a hyperlinked cell

Suppose I have a hyperlink in cell A1: =hyperlink("stackexchange.com", "Stack Exchange") Elsewhere in the sheet, I'd like to have formulas that get the link text and URL from A1, separately. I found ...
1
vote
1answer
20 views

How can I force a Google Spreadsheet to have column in one tab have the same width as the same column in a different tab?

Is there any way to force a Google Spreadsheet to have column i in tab j of same width as column i in tab k? In other words, can I configure Google Spreadsheet so that a column width change is always ...
1
vote
1answer
18 views

Remove any sheets not listed in an array

I have a Google App Script that has 6 sheets. onOpen, I would like to see if the user created any additional sheets, and if so, delete them. So far, I have: function onOpen(){ var ...
1
vote
1answer
33 views

Restricted view of a sheet, with each person seeing only their rows

I created a Google Spreadsheet. Column B contains the name of my employees. In Column C there is a task for every employee. Different employees need to have access to this document, but I prefer my ...
1
vote
1answer
67 views

Getting values from other sheets with a custom function

I've defined the following function, and use it in sheet1: function myfcn (sheetname,rowstart,rows,columnstart,rows){ var sheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname); ...
1
vote
1answer
26 views

How to receive a notification when changes are made to a specific tab in Google Sheets?

If would like to receive an email notification if a new row is added to a specific tab in a Google Sheets. The default notification rule settings are too limited and the Google Sheets Add-on Store ...
1
vote
1answer
41 views

Referencing a range in a sheet with variable name in another cell

When I would like to import (query) many columns in another sheet I use this code: =QUERY(sheet2!A1:D1;;) Now I would like to make this formula a little dynamic making sheet2 variable. So I put the ...
1
vote
1answer
24 views

How should I automate graph generation and image output in Google Sheets?

I am trying to send graphs of data to email recipients. I would like to output images of row-specific graphs based on cells in a Google Sheets document. In combination with Google Sheets/Drive, I am ...
1
vote
1answer
15 views

Drag and drop open csv in Google Sheets

The current process for opening a csv file in Google Sheets is cumbersome. Open Google Sheets > New Document > File > Import > Upload Tab > Drag in File > Select 'Replace current sheet' Would love ...
1
vote
2answers
32 views

How do I sum a column of cells by date?

I have a spreadsheet with these data (all of these dates and amounts are arbitrary): A B 12/1/2015 700 1/7/2016 100 1/13/2016 200 2/5/2016 300 3/19/2016 500 and another ...
1
vote
1answer
21 views

How do I add blank rows after each item from a FILTER() or QUERY() result?

Suppose you have a spreadsheet similar to: A 1 "text a1" 2 "text a2" 3 "text a3" 4 "text a4" ...and you'd like to create a new column containing each item followed by two ...
1
vote
1answer
37 views

Google Sheets overtime calculation if/and/or statements

I'm trying to set up a simple bi-weekly paycheck estimator in Google Sheets. Basically you put in the hours worked for Week 1 and Week 2, and I want it to automatically determine the amount of ...
1
vote
1answer
38 views

Google Sheet get value from a REST API

I need to include in a cell on a Google sheet the value of the ticker.last attribute in a JSON which is being constantly updated from a REST API located at https://www.mercadobitcoin.net/api/ticker/ ...
1
vote
1answer
185 views

Is there a way to generate edit response url for google form

I have a Google Form With a Google spreadsheet to store responses. In my spread sheet I have 4 column: name, email, revenue, and a fourth column Id which is used to identify the particular recipient. ...
1
vote
1answer
26 views

Keeping track of prices, recording every change above some threshold

An updated price comes in to a cell each minute "googlefinance()". In a second cell we start with a reference price which is typed in manually. The initial value is equal to the first price that comes ...
1
vote
1answer
47 views

“sort” function results in all of my data going to the bottom of the sheet

I am trying to use a form to create a list of jobs for our student employees, which exists as an editable Google sheet. I would like to be able to have any staff member add jobs that need to be ...
1
vote
1answer
23 views

How to turn a month string into a month int in Google Spreadhseets

I am trying to take the name of a month, such as October and turn that into the corresponding number for that month 10. Past making a long formula that checks for each month by string, and without ...