Stack Overflow
Stack Overflow

Questions tagged [google-sheets]

Do NOT share spreadsheets/images as the only source of data. Use markdown TEXT tables instead. Use this tag for questions about programmatically interacting with Google Sheets. Use with: [google-apps-script] for questions relating to the built-in scripting language, [google-sheets-formula] for questions relating to formula design or [google-sheets-api] and a language tag (eg:[python]) for questions relating to sheets API usage. Do NOT use with [excel].

Google Sheets is a cloud-based application and service for creating and collaborating on spreadsheet documents. The service supports an internal API for Google Apps Script as well as an External API.

Sharing your data:

Sharing your data helps other community members in visualizing your data. This can be done through

  • Tables(Mandatory):
    Markdown help can be found here. You can easily create a table using the formula: =ARRAYFORMULA("|"&A1:G20), if you want to share A1:G20. However, the first row A1:G1 must be a header row AND the second row A2:G2 should only contain dashes -- in all the cells.

  • CSV:
    Use File > Export to csv

  • Published Google sheets(in addition to text table):
    In the sheets file, click File > Share > Publish to web.

  • Share to others(in addition to text table):
    In the sheets file, click File > Share to others > Anyone with link. Note, however that sharing Google sheets this way makes your email address visible to public.

  • Screenshots(in addition to text table):
    This shows your data structure but makes it hard for anyone to copy data from the question for testing

If questions depends on external links/images, they will be closed. Text tables is mandatory.

Related tags:

52687 questions
91
votes
7 answers

Google Spreadsheet, Count IF contains a string

I have a column like this: What devices will you be using? iPad Kindle & iPad No Tablet iPad iPad & Windows How do I count the amount of people that said iPad? This formula does work for exact matches but not if it contains an additional value:…
Cody
  • 919
  • 1
  • 6
  • 3
90
votes
6 answers

How to address the current cell in conditional format custom formula?

I need to write a conditional format rule with a custom formula, that should trigger when the certain cell's (the cell in the 3rd row of the column of the current cell) value is "TODAY()", and the current cell is empty. I know how to check the other…
Vladimir Mikhaylovskiy
  • 1,955
  • 4
  • 19
  • 28
90
votes
6 answers

Selecting Multiple Values from a Dropdown List in Google Spreadsheet

The Google Spreadsheet looks like can only select one value in the dropdown list. Is there any way to select multiple values from a dropdown list in google spreadsheet?
user3084196
  • 925
  • 1
  • 9
  • 7
90
votes
6 answers

Google spreadsheet "=QUERY" join() equivalent function?

This question is concerning joining two databases in Google spreadsheet using =QUERY function I have a table like so in range A1:C3 a d g b e h c f i I have another table c j m a k n b l o I want the final table to look like this a d g k n b e h l…
jason
  • 3,811
  • 18
  • 92
  • 147
89
votes
4 answers

How can I copy a conditional formatting from one document to another?

Google Spreadsheets can copy cell format to destination cell, but it seems "Conditional formatting" information didn't be copied. Can we just copy the "Conditional formatting" from one document to another? I know we can recreate it from the…
user2575413
77
votes
10 answers

Getting Google Spreadsheet CSV into A Pandas Dataframe

I uploaded a file to Google spreadsheets (to make a publically accessible example IPython Notebook, with data) I was using the file in it's native form could be read into a Pandas Dataframe. So now I use the following code to read the spreadsheet,…
dartdog
  • 10,432
  • 21
  • 72
  • 121
75
votes
1 answer

Is it possible to 'prefill' a google form using data from a google spreadsheet?

I'm looking for a way to 'pre fill' a google form with specific data from a google spreadsheet. The form will have the same 'standard' questions for everyone, but the data in the first two question will be 'prefilled' with unique data from an…
Mr. B
  • 2,677
  • 6
  • 32
  • 42
74
votes
8 answers

How do I access (read, write) Google Sheets spreadsheets with Python?

I am wondering if you can point me to an example of reading/writing to/from a google doc/spreadsheet using python. I did look at google docs API here https://developers.google.com/google-apps/spreadsheets/ but not sure if I hit the right link. Also…
Abhi
  • 6,075
  • 10
  • 41
  • 55
72
votes
5 answers

How do you add UI inside cells in a google spreadsheet using app script?

I'd like to add buttons to specific cells in Google docs spreadsheet. The apps script UI documentation talks about how to add a new panel, but it's not clear how UI in that panel could be attached to specific rows or cells. Is it possible to add…
James Moore
  • 8,636
  • 5
  • 71
  • 90
71
votes
3 answers

Get Sheet By Name

I'm trying to get the following formula to work: function setDataValid(range, sourceRange) { var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build(); range.setDataValidation(rule); } function onEdit() { …
Michiel van Dijk
  • 783
  • 2
  • 8
  • 19
70
votes
4 answers

ArrayFormula and "AND" Formula in Google Sheets

In Google Sheets, when using ArrayFormula with AND formula, I don't get the results as it should be. A B 6 7 In C1 I put formula as: =and(A1>5,B1>6) then I get True. If in D1 I put formula as: =ArrayFormula(and(A1:A>5,B1:B>6)) I get the…
Hossein
  • 720
  • 1
  • 5
  • 7
70
votes
5 answers

Concatenate a range of cells with a comma

I'd like to combine a range of cells of data so that it comes out with just one text string and a comma + space between each one. I have been successful in using concatenate: =ArrayFormula(concatenate(C3:F&", ")) but there are extra commas that…
Niels van der Tak
  • 705
  • 1
  • 6
  • 6
70
votes
5 answers

Count number of cells with any value (string or number) in a column in Google Docs Spreadsheet

I have several columns in Google Spreadsheet. I would like to count how many cells have a value (number or string) and display that number at the top of the column. For example: Hello World 123 Some string The above column would give me the…
steakpi
  • 953
  • 1
  • 6
  • 11
69
votes
3 answers

How do I locate a Google SpreadSheet ID?

https://developers.google.com/apps-script/guides/rest/api#parameter_and_return_types When using the Google Execution Api for Java, the ID of the spreadsheet isn't very easy to understand. Here is the code snippet: // Initialize parameters for that…
RamelHenderson
  • 2,151
  • 2
  • 13
  • 12
68
votes
16 answers

How to extract URL from Link in Google Sheets using a formula?

I have copied from a website a series of hyperlinks and pasted them in a google sheet. The values show up as linked text, not hyperlink formulas, and are still linked correctly. For each row, I'm trying to extract the URL ONLY (not the friendly…
iamtoc
  • 1,569
  • 4
  • 17
  • 24
1 2
3
…
99 100