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
126
votes
4 answers

Check cell for a specific letter or set of letters

In a Google Spreadsheet, I want to use a formula that will output a certain text or number if a certain cell contains certain letters. For example, if I put =IF(A1="Black";"YES";"NO") and A1 is equal to "Black" - it outputs "YES" correctly. However…
user1799724
  • 1,521
  • 3
  • 10
  • 6
124
votes
22 answers

Refresh data retrieved by a custom function in Google Sheet

I've written a custom Google Apps Script that will receive an id and fetch information from a web service (a price). I use this script in a spreadsheet, and it works just fine. My problem is that these prices change, and my spreadsheet doesn't get…
tbkn23
  • 5,205
  • 8
  • 26
  • 46
122
votes
8 answers

Download link for Google Spreadsheets CSV export - with Multiple Sheets

I'm trying to find a link which allows me to download a CSV formatted version of my Google Spreadsheet. Currently I'm using: https://docs.google.com/spreadsheets/d/DOCID/export?format=csv This works great except that it only download the first…
PlacateTheCattin
  • 1,356
  • 2
  • 11
  • 10
119
votes
7 answers

How to union ranges in google spreadsheets

I want to union ranges from any Google spreadsheets. The example Sheet1!A:A {12, 131, 45} Sheet2!A:A {12, 131, 46} The unknown function =formula_for_union_range(Sheet1!A:A; Sheet2!:A:A) should return {12, 131, 45, 12, 131, 46} The question How…
contributorpw
  • 4,739
  • 5
  • 27
  • 50
113
votes
24 answers

Selecting the last value of a column

I have a spreadsheet with some values in column G. Some cells are empty in between, and I need to get the last value from that column into another cell. Something like: =LAST(G2:G9999) except that LAST isn't a function.
cambraca
  • 27,014
  • 16
  • 68
  • 99
111
votes
8 answers

Convert Unix Epoch Time to Date in Google Sheets

I have a sheet with a column of unix epoch times (in seconds): 1500598288 How can I convert these into normal dates?
Mike Bjorge
  • 1,941
  • 2
  • 11
  • 13
109
votes
6 answers

ISO-8601 String to Date in Google Sheets cell

I have a bunch of ISO-8601 formatted strings in a column of my sheet. How can I get google sheets to treat them as Dates so I can do math on them (difference in minutes between two cells, for example)? I tried just…
Bob Kuhar
  • 10,838
  • 11
  • 62
  • 115
107
votes
5 answers

How do I avoid the "#DIV/0!" error in Google docs spreadsheet?

I have a column with average(K23:M23) that starts out with #DIV/0! when the K23 through M23 cells are empty. Preferably I'd like to only do the average of cells that contain non-zero, non-blank values. I think it's possible using the query…
Aaron
  • 2,154
  • 5
  • 29
  • 42
106
votes
8 answers

Accessing Google Spreadsheets with C# using Google Data API

I'm having some information in Google Spreadsheets as a single sheet. Is there any way by which I can read this information from .NET by providing the google credentials and spreadsheet address. Is it possible using Google Data APIs. Ultimately I…
blitzkriegz
  • 9,258
  • 18
  • 60
  • 71
105
votes
30 answers

Determining the last row in a single column

I have a sheet with data in cols A through H. I need to determine the last row in column A that contains data (it's all contiguous - no gaps in the data/rows). There is also data in the other columns that have more rows of data than column A, so I…
5th4x4
  • 1,497
  • 8
  • 21
  • 27
103
votes
4 answers

How to negate 'isblank' function

I want to do "is not blank" in a custom formula. There is a isblank() function but I can find neither an isnotblank() function nor a way to say not, such as ! or ==False. How can I say is not blank?
cja
  • 9,512
  • 21
  • 75
  • 129
98
votes
3 answers

Get value from the cell above

Is it possible in Google Spreadsheets to reach the value of the cell just above? Meaning: In one cell A2 I want to display the value from the cell above, that is A1. Not because it is that coordinate, but because it is the cell above. I do this by…
Steeven
  • 4,057
  • 8
  • 38
  • 68
97
votes
8 answers

count distinct values in spreadsheet

I have a Google spreadsheet with a column that looks like this: City ---- London Paris London Berlin Rome Paris I want to count the appearances of each distinct city (so I need the city name and the number of appearances). City |…
Marius Bancila
  • 16,053
  • 9
  • 49
  • 91
93
votes
2 answers

Using A Cell To Reference The Name Of Sheet In A Formula For Google Sheets

is it possible to place the name of the first worksheet in a cell and then use it in a formula to reference information for my second sheet? I am using Google Spreadsheets, and I have one worksheet that houses scheduling data for employees in…
Richard Ennis
  • 939
  • 1
  • 6
  • 3
91
votes
10 answers

Is there a Google Sheets formula to put the name of the sheet into a cell?

The following illustration should help:
orschiro
  • 19,847
  • 19
  • 64
  • 95
1
2
3
…
99 100