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

learn more… | top users | synonyms (2)

0
votes
0answers
4 views

How to email a row in google sheets

First post. I have just started to learn to code (2 days in) but this is way over my head for now. I want to be able to email the contents of a row (all cells in that row) by right clicking the grey ...
0
votes
1answer
12 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?
1
vote
1answer
9 views

Google Spreadsheets: 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 ...
1
vote
1answer
7 views

Google Spreadsheets: Multiple cells with same drop-down list, but not 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
9 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
17 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
22 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
11 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 ...
2
votes
1answer
35 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
12 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
11 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
16 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
19 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
14 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
9 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
21 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 ...
-3
votes
0answers
19 views

How to retreive data from non-public Google Spreadsheet as JSON in Android app? [closed]

I need to access exactly non-public spreadsheet. I can't use authorized http request, because of security policies. I think I must use Google API. Can I add it to Gradle in Android studio. Please ...
2
votes
1answer
28 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
20 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
34 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 ...
-2
votes
0answers
8 views

categorising info from google form responses

staff will be filling in a google form on how many hours they have looked after a group at school for e.g. soccer for say 10 hours. I then need on a second sheet specific data to be categorised. I ...
5
votes
1answer
35 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
49 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
11 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
20 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
22 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
40 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
36 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
69 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
21 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
39 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 ...
0
votes
0answers
15 views

SUMIF calculation by getting data from new spreadsheet

I'm creating monthly and yearly report in google sheet (google drive). i'm trying to perform calculation by matching specific case from another google sheet. i used the below function to link with the ...
3
votes
1answer
27 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 ...
1
vote
1answer
15 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.
1
vote
1answer
18 views

Auto-update a google form?

Is it possible to write code that automatically changes the questions on a Google Form? Would love to be able to automatically sync our signup form with a spreadsheet which lists the chapters around ...
2
votes
2answers
26 views

Can I automatically email the row contents I enter in Google Sheets?

I have a Google Spreadsheet where I enter a row of data with any changes to my office schedule for the day. Is there a way to automatically email this row of data (not a link to the spreadsheet, but ...
3
votes
1answer
39 views

How do I interleave two lists or data in Google Spreadsheets?

I want to combine two lists in Google Spreadsheet so that the cells interleave. Both lists have the same amount of elements. So that: 1 A 2 B 3 C becomes: 1 A 2 B 3 C ...
2
votes
1answer
23 views

Google Sheets: Format number as Mhz/kHz, or MB, kB etc

I want to make a spreadsheet with Google Sheets to compare microprocessor's specifications. One column will show processor speed, another will show program space, another for RAM space and so on. ...
3
votes
1answer
39 views

How to split multiple values in one cell into separate cells?

I'm using ImportHTML to pull in data scraped using another tool called Import.io, as you can see in the example spreadsheet here. However, when I pull in data using ImportHTML, it's pulling 3 years ...
2
votes
1answer
25 views

Concatenate inside ImportRange inside Query

I found this formula: =QUERY(IMPORTRANGE("key","A:Z"),"SELECT Col1, Col2, Col3 WHERE Col1 contains 'value' ORDER BY Col1") Here and I'd like to add a concatenate function where 'value' is taken ...
3
votes
1answer
60 views

How can I create a URL for a specific cell in Google Sheets?

I am a teacher who is trying to create QR codes for book reviews to be placed on books in the library. I have collected all of my students' book reviews via a Google Form, so that their answers are on ...
5
votes
1answer
31 views

How to refer to a cell in the same column at given row?

I would like to get a cell in a particular row above the current cell. This is to be used in conditional formatting, so I can't specify a particular cell, e.g. A$4—the reference would be the same for ...
2
votes
1answer
26 views

How to embed a Google spreadsheet (or any kind of tables) in Medium?

I tried to embed a Google spreadsheet in Medium without success. I could get the post the title but not the data. Knowing that I used the "publish" feature of Google spreadsheet. If not possible, is ...
2
votes
3answers
85 views

How to calculate difference in hours between two time fields which runs from one day to the next

I've found a formula that will calculate how many hours between 2 time perfectly well if they are in the same day, but as soon as the times go from one night to the next morning I end up with a minus ...
2
votes
1answer
17 views

Calling importRange from within another function

I have a bunch of separate spreadsheets and I'm creating an overview spreadsheet which pulls data from each of the others. I also need to apply the COUNTUNIQUE formula to that data. If I simply type ...
1
vote
1answer
18 views

Sum of cells within a given range of dates

I'd like to calculate my revenues in a given trimester, based on the invoice total and the date it was paid. Column B - invoices to add up Column E - date paid Here is my attempt (which comes up ...
2
votes
1answer
12 views

How to convert all numbers regardless of decimal separator into valid number of current locale in google sheet

I have a column with text looking like this: 123,123 123.123 123,123 123.123 I would like another column to parse this into decimal numbers, disregarding the different decimal separators (dot . and ...
3
votes
1answer
24 views

How to move a standalone chart back to an existing sheet

I regret moving my chart to a new spreadsheet in Google Spreadsheets. How do I move it back? Using the revision history to undo the edit is not an option, since it has been a while since the original ...
-1
votes
1answer
28 views

Automatically group data by month

I am using Google Sheets and I would like to display monthly in and out. date units unit cost cost 2015-01-05 200 0.24275 -48.55 2015-01-05 1 1.43 -1.43 2015-01-09 ...