One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

3
votes
1answer
38 views

Is there a way to stripe rows based on change of date in Google Spreadsheets

I would like to not zebra stripe a spreadsheet in Google but to have the sections change color and alternate between dates. So 7/1/2015, even if there are 5 rows dated this would be color A, 7/2/2015 ...
2
votes
2answers
41 views

Prevent “invoked too many times per second” error in Google Apps script

I need a number to be converted to a name. In B1:B1000 I have numbers. In C1:C1000 I has function name. On writing C30 got an error: invoked too many times per second ... how do I do function on range ...
-1
votes
1answer
22 views

Restrict the valid values in one cell, based on the value in another cells [closed]

I am trying to prevent a date being entered into a cell if the date precedes the date in another cell.
2
votes
2answers
22 views

Sum all expenses of a given type given a list of expense types in a separate sheet

I use Google Sheets to keep track of my expenses. The file currently consists of: Sheet 1: "Expense Types" Sheet 2: "2015 Details". The first column of Sheet 1 contains the list of admissible ...
0
votes
1answer
31 views

Using Query with Importrange and comparison of dates

I have no experience with this, but I'm trying to set up a very extensive spreadsheet for work. I've got everything to work so far, but now I'm stuck. I created a Google form that employees complete ...
1
vote
0answers
10 views

How do do Conditional Formatting to compare cell contents between to Worksheet tabs [duplicate]

So, I've got two tabs in a Google Spreadsheet. I want to compare the contents and see which are different. Ideally, I'd like to use Conditional Formatting to format the cells that don't match. I.e., ...
3
votes
1answer
10 views

Set a revision point in Google Spreadsheets; or compare revisions between two arbitrary dates

Google Spreadheets's Revision History is very handy. But, I often do things like: Copy a Tab Edit it When finished, copy it over the original. I want to see only the changes made in step 2. But I ...
1
vote
1answer
14 views

Formatting cells based on upcoming date in Google sheets

I am trying to format a cell to be highlighted red if the following conditions are true: the future date associated with that row (which is in a separate cell) is less than 21 days from today, and ...
2
votes
1answer
40 views

Search Google Drive files directly from the Chrome search bar?

Is there a way (perhaps a Chrome extension) to show Google Drive search results in my Chrome autocomplete menu? For example in this image, if I had a Google Doc or Google Sheet called "orange juice" ...
2
votes
1answer
26 views

Highlighting duplicate data in rows

I have a query and I cant figure out why it's not correct. I have two lists of data and I want to highlight the matching data in the separate lists. Below is the query I have constructed ...
3
votes
1answer
39 views

Google Sheets notification triggered from function result

I have a google form that populates a spreadsheet with a timestamp and two numbers. For technical reasons I can't have the numbers passed directly into a database but they need to be copy-pasted ...
3
votes
1answer
25 views

Restore a Google Sheet

I was entering information on my Google Sheet and walked away. Then when I went to entered another number in a cell an error message came up and the entire sheet disappeared. There must be a way to ...
1
vote
0answers
13 views

Deleting common row in Google Sheets [duplicate]

I have a Google spreadsheet and I am trying to obtain the difference/complement of two lists where the second list is a subset of the first. Is there any function which does the below? Example: ...
1
vote
0answers
26 views

Google spreadsheet dynamically populated list

So I am working on creating a spreadsheet for the game League of Legends. Specifically equipping the various champions with items. The calculations for the basic bonus to stats are simple but many of ...
2
votes
2answers
92 views

How to reference a fixed sheet name in Google Spreadsheet

I am doing reports from a set of data in Google Spreadsheet. As of now, I have several sheets, among them Raw Data and Processed Data. In my Processed Data sheet, I use formulas such as: SUM('Raw ...
2
votes
1answer
32 views

Customizing filter in Google Spreadsheet

I'm trying to create a Spreadsheet for work purposes but I can't get it the way I want. If I do it in Excel it works but I can't match it in Google Spreadsheet. I don't even know if it's possible. ...
1
vote
1answer
22 views

Automated Email Google Spreadsheets

Someone was kind enough a while ago to share with me a script that allowed me to send myself an email automatically as long as I had a time trigger. Yesterday, Google changed the OAuth in Apps Script ...
0
votes
0answers
14 views

Linking to a specific Google sheet after it has been published?

Currently I have a Google spreadsheet with multiple sheets. I want to be able to link to each of these sheets separately so that I can have an index sheet people can just refer to. I know that within ...
1
vote
1answer
30 views

Add cells based on the content of another column

I have this sheet and I am trying to add the values of June but the issue that I have is that these values will update often with so I need to find a formula that accounts for that. The other issue is ...
2
votes
1answer
57 views

How to post responses from Google forms to a specific sheet in a Google spreadsheet

I have a form, a Google form, and I'd like to post user's responses to a specific sheet in the Google spreadsheet. Is this possible?
2
votes
1answer
32 views

Grab text of first row of column with last data value

Essentially, I want the values in column D to be the value of the header row of the column with the last data value. For example, if H27 is the last cell with data in row 27, I want D27 to be the ...
2
votes
1answer
60 views

Multiple cells with same drop-down list, but disallowing duplicate selections

I'm looking to create a few drop downs in my spreadsheet where they share the same list of choices, but you can't select the same option twice. This is for choosing first, second, and third choices ...
1
vote
0answers
14 views

Narrow sample space down with Google Forms evaluation

I got the results from a survey I did with Google Forms. The survey results are stored in a Google Spreadsheet (150 rows). I really like the feature to get the results in diagrams automatically, but ...
1
vote
1answer
33 views

Conditional Formatting: no color when cell is empty

I am using this custom formula for my sheet: =if(B2<((B$7/4)*$A2), 1, 0). However, this also highlights the rest of the cells that are empty. How do I exclude empty cells from this formula?
2
votes
1answer
49 views

Clear range of cells

I'm trying to make a script for Google Spreadsheets (which is my first time), by clearing a range of some cells. So: for example I want to clear D5:D12 but not D13 and D14, but then again I want to ...
2
votes
1answer
16 views

Google Sheets “Skipping blanks”

I have a huge data sheet that is being updated every week. It has about 2,000 colums and 6 rows. Every cell each week is new (Its a list of average prices, so the names of the items remain the same ...
3
votes
1answer
67 views

Using query to find the entries in one column which are not in the other

I am using Google Sheets to track changes in a group's roster. Cells B2:B240 contain the old list of names, and C2:C240 contains the new list. Both lists are alphabetized. There is a lot of overlap ...
2
votes
1answer
21 views

How to compute the maximum of Column B after having filtered by the contents of Column A?

I have data in two columns (say A2:B20). To get what I want, I have to filter by the first column, and then get the maximum value in the second column of the filtered array. Can this be done without a ...
1
vote
1answer
27 views

Sort randomly in a Google Script

I found this script to sort a Google Spreadsheet but I need to sort everything randomly instead of alphabetically, is there a way to sort it randomly every time this script runs? > // LinkBack to ...
2
votes
1answer
24 views

Import Google Contacts to Google Spreadsheet

Google notice me : You do not have permission to call getContacts (line 3) Here is my code: function importFullName(input) { var CONTACT = ...
1
vote
1answer
29 views

How do I organise my data in Google SpreadSheets

I have separate sheets based on month's of the year. Each time I create a new month I copy the template sheet and fill in the data. Therefore each sheet has the same layout it's only the data that ...
2
votes
1answer
26 views

Highlight all cells in a range that contain a formula in Google Sheets

I am looking to apply conditional formatting to all the cells in my sheet and highlight cells that contain a formula. I know that I can format cells based on a custom formula, and I know that I can ...
0
votes
0answers
10 views

Subtract Data for Graph in Google Sheets

I'm pulling data into Google Sheets via the Google Analytics plugin, and the report runs automatically, replacing the data on a sheet called Website I then (on a different sheet) have various graphs ...
2
votes
1answer
24 views

substitute() multiple terms in Google Spreadsheets

I'm trying to simplify semi-complex text strings into much simpler ones - specifically names into hashtags. For example: Tomáš Rosický into #TomasRosicky or Alex Oxlade-Chamberlain into ...
2
votes
1answer
39 views

Using HLOOKUP for date values

I'm trying to get the HLOOKUP function working in my sheet but it's not working like it should. The function is: =HLOOKUP(TEXT(NOW(), "yyyy-MM-dd"), INDIRECT(TEXT(NOW(), "MMMM") & "!B1:AE5"), 3, ...
1
vote
2answers
37 views

Google Sheets permissions

I built two separate spreadsheets in Google Drive and spreadsheet "A" has a script that when run, will copy the input data into spreadsheet "B". When I test it, it works perfectly. But I have other ...
1
vote
0answers
19 views

How to calculate interest rate from start and end amounts in Google Sheets? [duplicate]

What is the formula and result for working out the interest rate required for making £38106 into £73036 in 20 years? (As if you put £38106 into a bank account and left it for 20 years, never taking ...
2
votes
1answer
49 views

Create a 7 day running total and running average using ARRAYFORMULA

There's a question on this forum that asks how to get a 7 day running total and running average. It's available here and it reads like this: I have a Google Spreadsheet tracking figures day by ...
5
votes
1answer
57 views

Conditional formatting of a row header based on the values in the column

I have a table I'm using to list recipes and their ingredients. Ingredients are rows, recipes are columns, with a column next to the ingredients denoting if that ingredient is in stock. Like so: ...
2
votes
1answer
51 views

How to automatically increase the exponent in Google Spreadsheet columns?

I have asked a similar question before How to automatically increase the exponent in Google Spreadsheet? But the new question is: In a Google Spreadsheet, I put in the following numbers in a ROW, ...
1
vote
1answer
15 views

Link in Google Sheets to either create a new document or link to the one that was previously created

I'd like to provide a column in a spreadsheet that allows the user to create a note for the column by clicking on a link. If the note has never been created, it will create the link to a new document ...
2
votes
1answer
34 views

SUMIF using NOT ISDATE

I want to sum across a range of fields, but only if the cell contains a number, not a Date. I would love to use something like SUMIF(A2:Z2,"!ISDATE()",A2:Z2) But the criteria in SUMIF is incredibly ...
1
vote
1answer
51 views

Google Sheets Count Unique value with multiple criteria

I am working on a Google spreadsheet as below: Walkin date | Name | Phone | Status | Remarks 01/06/15 | Client1 | 1001 | Open | Will join soon 03/06/15 | Client2 | 2002 | Sales | ...
3
votes
2answers
46 views

Replace chunks of HTML with double quotes using SUBSTITUTE

I want to replace the following chunks of html contained in a cell: Original <table class="generic"> Replacement <table class="data-table" id="product-attribute-specs-table"> ...
2
votes
1answer
42 views

Delete duplicate text within a cell

I've got cells in a column populated with data pulled from another tab using A1= Results_ES!C1 and A2= Results_ES!C2 etc. Some of the data is duplicated whithin the cell, e.g. Cell A1= Black Black ...
4
votes
1answer
133 views

How to access a gauge chart in Google Spreadsheets?

I have a gauge chart in my spreadsheet. I am looking for a line for accessing it. function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Gauge"); ...
1
vote
1answer
46 views

Relative Conditional Formatting with Google Sheets

I'm trying to use conditional formatting in the goals area. Currently the calorie goal (J10) turns green as long as I stay under it's number, and it's compared to the total calories for the day (J3). ...
3
votes
1answer
46 views

Create keyboard shortcut for Chrome OS to move to next sheet in Google Sheets

I really need to create a keyboard shortcut to move from one sheet to the next in Google Sheets in Chrome OS. In Mac this exists, but does not on Chrome OS. There is an extension called Shortcut ...
3
votes
1answer
42 views

Attach image url then email using Google Sheets

What is the correct syntax for emailing an image url in a row as an attachment? I'm trying to loop through all rows within a Google Sheet and attach an image url as the image. I can send the correct ...
0
votes
1answer
17 views

Is it possible to filter columns in Google spreadsheets [closed]

Is it possible to filter columns in Google spreadsheets i.e. I want to see suppose only 4 out of the existing 20 coulmns.