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
31
votes
5 answers

Multiple IF statements between number ranges

I'm trying to set up a formula with multiple IF statements between number ranges but I'm seeing the error: Formula parse error Here is the forumula: =IF((AND(A2>0,A2<500),"Less than 500", If(AND(A2>=500,A2<1000),"Between 500 and 1000"), …
Kristin
  • 431
  • 1
  • 4
  • 7
31
votes
8 answers

How to use GOOGLEFINANCE(("CURRENCY:EURAUD")) function

This function: GOOGLEFINANCE(("CURRENCY:EURAUD")) works well in Google Sheets, I have searched the web up and down for some documentation regarding this function and how it is used, the closest I've got was this…
Ayyash
  • 4,257
  • 9
  • 39
  • 58
30
votes
17 answers

Convert an excel or spreadsheet column letter to its number in Pythonic fashion

Is there a more pythonic way of converting excel-style columns to numbers (starting with 1)? Working code up to two letters: def column_to_number(c): """Return number corresponding to excel-style column.""" number=-25 for l in c: …
paragbaxi
  • 3,965
  • 8
  • 44
  • 58
30
votes
3 answers

Extract substring after '-' character in Google Sheets

I am using the following formula to extract the substring venue01 from column C, the problem is that when value string in column C is shorter it only extracts the value 1 I need it to extract anything straight after the - (dash) no matter the length…
Roggie
  • 1,157
  • 3
  • 16
  • 40
30
votes
3 answers

Reading whole Google Spreadsheet with Sheets API v4 Java

I would like to read a Google Spreadsheet like described in the Java Quickstart https://developers.google.com/sheets/quickstart/java The Quickstart explaines how to read data from a give range ..... String range = "Class Data!A2:E"; ValueRange…
Michael Meyer
  • 2,179
  • 3
  • 24
  • 33
30
votes
8 answers

Highlight entire row when cell is active

How to design a sheet script that would result in an active row being highlighted? I would like to have an entire row change color of font or background when one cell in that row is active. I don't want the trigger to be any specific value in the…
Shawn Hamilton
  • 309
  • 1
  • 3
  • 3
30
votes
8 answers

How do I rename a (work)sheet in a Google Sheets spreadsheet using the API in Python?

I have been trying/looking to solve this problem for a long while. I have read the documentation for gspread and I cannot find that there is a way to rename a worksheet. Any of you know how to? I would massively appreciate it! There is indeed…
Ilden Gemil
  • 301
  • 1
  • 3
  • 3
30
votes
7 answers

Google spreadsheet direct download link for only ONE sheet as excel

I was wondering if its possible to download say only sheet 1 of a google spreadsheet as excel? I have seen few SO posts that show the method to export the WHOLE sheet as excel, but I need to just export one sheet. Is it at all possible? and if yes,…
rahulserver
  • 10,411
  • 24
  • 90
  • 164
30
votes
5 answers

Format a Google Sheets cell in plaintext via Apps Script

Using Google Apps Script, I want to set the format for a Google Sheets cell to plain text. The reason I want to do this is because I want to show a date in the U.S. date format (MM/DD/YYYY). [We can assume the locale in the OP's Google account is…
Anton Pil
  • 329
  • 1
  • 3
  • 7
29
votes
5 answers

Embed editable Google Docs spreadsheet without header & toolbar

I have the requirement to embed a Google Docs spreadsheet on a webpage, but it needs to be just the editable cells, without the Google navigation and toolbar at the top. I can't find a reference to the querystring variables for embedding Google docs…
Dan
  • 5,836
  • 22
  • 86
  • 140
29
votes
4 answers

In Google sheets, how does one either return a blank cell or perform a function based on another cells lack of content?

This is what I have thus far: =if(ISBLANK(A:A), (A:A+TIME(2, 0, 0)), "") I'd like the script to add 2 hours to A:A or just remain blank if there is no data inside A:A Any ideas would be greatly appreciated. Thank you :)
Chips147
  • 595
  • 3
  • 6
  • 12
29
votes
3 answers

How to use native spreadsheet functions in google apps script?

Is it possible to use native spreadsheet functions such as sum() or today() in google apps script, when writing code for google Spreadsheet? If so, how do I do it?
user3347814
  • 1,138
  • 9
  • 28
  • 50
29
votes
2 answers

How to hide certain columns for a user?

On a shared Google spreadsheet: I don't want a specific user in my domain to view columns with sensitive data, but this person should still be able to edit the rest of the spreadsheet. I tried creating a 'master' spreadsheet and using importrange…
lee
  • 1,036
  • 2
  • 14
  • 24
29
votes
4 answers

Fill down - Google Apps Script for Spreadsheets

Is there a function that can fill down data to all empty cells from the row above? If not, could anyone help me out with a Google Apps Script (or other solution) that can go through every row in my google spreadsheet, check for empty cells, and if…
Mille
  • 713
  • 2
  • 8
  • 10
29
votes
3 answers

Using built-in spreadsheet functions in a script

I'm using Google App Script for the first time. I'm using it on a Google Doc spreadsheet. I'm trying very simple functions, just to learn the basics. For example this works: function test_hello() { return 'hello'; } But I'm puzzled by this…
mascip
  • 1,347
  • 1
  • 14
  • 16
1 2 3
…
99 100