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

learn more… | top users | synonyms (2)

1
vote
1answer
650 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
58 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
852 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
141 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
479 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
2answers
5k views

Google Spreadsheet; Function to Search a Sheet for String and Return Value of Next Column

Here is what I have so far. it will: Search for keyword in my other sheet (JSON) When it finds the keyword (modname) it returns the row # The keyword is on the A column. What I want is for a ...
1
vote
1answer
5k views

How can I group a pivot table into weeks or months?

In Microsoft Excel, in a pivot table, you can right click on the dates - while within the pivot table, on a date and select group (from the drop down menu) - then you can choose different groupings, ...
1
vote
1answer
537 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: =ArrayFormula(sum(if(mod(row(J:J)...
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
119 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
1answer
1k views

Google Spreadsheet: Count values from last 30 days

Here is a sample spreadsheet you can view and edit: https://docs.google.com/spreadsheet/ccc?key=0AnWwBCPp8kISdGc4bExyYV8xOTVRbnRkaXNQSzBWalE&usp=sharing. How can I count the number of successes ...
1
vote
2answers
617 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
370 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
3k views

How to query for count of dates in Google Docs?

I have a Google Spreadsheet of line items. One of the columns is a Date field. How can I query (or use a formula) to, for example, get the count of records where the Date field is within February of ...
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
13 views

How to know who edited a Google Sheet anonymously?

Is there any way I can find who edited a Google Sheet anonymously? I have shared a Google Sheet with a group of users using a link but it was edited by an anonymous user. I want to know is there any ...
1
vote
1answer
14 views

Conditional Formatting if all cells fail to meet a certain criteria

This is the spreadsheet I'm working on. I am trying to figure out a formula for conditional formatting so that it only highlights the cells in red if none of the cells from columns N to Y of the ...
1
vote
1answer
32 views

Paste value only without formula for hyperlinks

Consider the following: A | B | C ------------------ | -------- | ------------------- =HYPERLINK(C2, B2) | Google | http://google.com =HYPERLINK(C3, B3) | Facebook | http://...
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
31 views

How to refer to a cell's content from a query in Google Spreadsheets? [duplicate]

=QUERY('F/O OFF'!1:1000, "select H, J where B='CIN'label H'', J''") Right now this formula works fine, but i have to change the "where B=" for each row in the sheet. I want to get it so where B= (...
1
vote
1answer
30 views

Compute the total of every row with arrayformula

In my spreadsheet, I need to add up each individual row. It does that if I drag the formula down in Y, but then when a new row is added to the data from a Google form, it won't copy the formula. So I ...
1
vote
1answer
63 views

Filter View, Text Starts With in Custom Formula

In Google docs (spreadsheet) under Filter View, I am trying to write a custom formula for multiple conditions. For example, in column O, I may have "Jury Room" and also "Jury Rooms" and so I am ...
1
vote
1answer
27 views

Timestamp code for Google Sheets

I am using this script in order to put a time stamp in column B when someone edits in a row. However, I need it to place the time stamp if someone edits a row in column A. I don't need it to place a ...
1
vote
2answers
56 views

Row by row comparison with FILTER returning error

I am trying do a row by row comparison of two separate sheets, and display the unequal rows on a third sheet. As an example, rows three and five on Sheet1 are different from Sheet2 and I want to ...
1
vote
1answer
21 views

What is a cut and paste approach to converting a chunk of a google spreadsheet to markdown table?

I maintain my web site in a version of markdown, with the common table extension. I maintain my inventory as a google spreadsheet. I'd like a quick way to copy paste a chunk of a spreadsheet, and ...
1
vote
1answer
13 views

Google sheets formula to countunique rows with a condition

I have a sheet where I keep track of some expenses. the data is formatted like this: Vendor A | Plumbing | $250 Vendor B | Cleaning | Vendor A | Cleaning | $19 Vendor C | Washing | $20 | ...
1
vote
2answers
42 views

An arrayformula to find the previous non-empty cell in another column and find the last row to work with

I would like to do something similar to what was solved in the question: An arrayformula to find the previous non-empty cell in another column The problem was: If I have a column "input", I would ...
1
vote
1answer
39 views

Conditionally format data in spreadsheet column, but only when it changes

Say I have this data in a Google Spreadsheet: cat cat mouse mouse mouse cat dog dog And I only want to apply conditional formatting to the ones in brackets [] (so I can hide them but still ...
1
vote
1answer
51 views

Is it possible to use the contents of a cell in a custom conditional formatting rule in Google Sheets?

For example, given the sheet below: I'd like the background colours of columns B-D to change automatically when I update the values in column A Is that possible? I'm looking for a solution that ...
1
vote
1answer
95 views

Google Apps Script Exceeded maximum execution time

My code for an API pull has been timing out a lot lately. Here is my code (note that I have changed some variables for privacy): // SHEET DATA var ss = SpreadsheetApp.getActive(); var data_sheet = ...
1
vote
1answer
24 views

Apply formatting and formulas to another sheet?

I have a template tab that contains all formulas and formatting. Then one tab for each user. I know how to reference cell content with =template!A1. How to reference formula that reference current tab?...
1
vote
1answer
26 views

Using a second table to determine the value of a cell in the first table

I have a Google Spreadsheet with table 1 'city, country, region'. On a separate tab there is table 2 'countries and regions'. What statement can populate the 'region' column in table 1, based on the ...
1
vote
1answer
15 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
1answer
28 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
21 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
71 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
128 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
72 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
188 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
81 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
94 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
45 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
61 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
45 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
54 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
451 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
31 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
95 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 taken,...
1
vote
1answer
30 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 ...