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
68
votes
6 answers

How to format a duration as HH:mm?

In the new Google sheets there's a way of formatting a number as a duration. Format -> Number -> Duration. 1 is formatted as 24:00:00 1.2 is formatted as 28:48:00 1.5 is formatted as 36:00:00 0.03125 is formatted as 0:45:00. I don't need the…
Christiaan Westerbeek
  • 10,619
  • 13
  • 64
  • 89
68
votes
2 answers

Google Spreadsheet Formula to Use Current Row in Function

I want to calculate the product of two different columns (e.g., A & B) for each row on my spreadsheet. For a single row, this would work by using =PRODUCT(A1:B1), but I can't figure out how to get the function to use the current row instead of a…
whoadave
  • 758
  • 1
  • 5
  • 6
68
votes
9 answers

Iterate over range, append string to each

I have a range of cells selected in a Google Sheets (activerange). I want to iterate over every cell in that range, and add a string to the end. The string is always the same, and can be hard coded into the function. It seems like a really simple…
ezuk
  • 3,096
  • 3
  • 30
  • 41
67
votes
9 answers

QUERY syntax using cell reference

I'm having trouble figuring out a fairly simple QUERY statement in Google Spreadsheets. I'm trying to use a cell reference instead of static values and I'm running into trouble. Below it the code I'm using, but I keep getting a "Error: Formula parse…
Mr. B
  • 2,677
  • 6
  • 32
  • 42
67
votes
5 answers

How do I add formulas to Google Sheets using Google Apps Script?

How do I add a formula like: =SUM(A1:A17) to a range of fields using Google Apps Script for Google Sheets?
Pablo Jomer
  • 9,870
  • 11
  • 54
  • 102
66
votes
17 answers

Faster way to find the first empty row in a Google Sheet column

I've made a script that every few hours adds a new row to a Google Apps spreadsheet. This is the function I've made to find the first empty row: function getFirstEmptyRow() { var spr = SpreadsheetApp.getActiveSpreadsheet(); var cell =…
Omiod
  • 11,285
  • 11
  • 53
  • 59
66
votes
8 answers

How to define global variable in Google Apps Script

I see most examples from Google is they use only functions in a single giant script. e.g. https://developers.google.com/apps-script/quickstart/macros But in our style, we usually write all functions under a single namespace, such as MyCompany =…
Ryan
  • 10,041
  • 27
  • 91
  • 156
65
votes
9 answers

Hash of a cell text in Google Spreadsheet

How can I compute a MD5 or SHA1 hash of text in a specific cell and set it to another cell in Google Spreadsheet? Is there a formula like =ComputeMD5(A1) or =ComputeSHA1(A1)? Or is it possible to write custom formula for this? How?
HDB
  • 735
  • 1
  • 5
  • 5
65
votes
6 answers

Conditional Formatting from another sheet

I'm trying to have a cell on Sheet A check if it's either > or < the value in a cell on Sheet B, then change its color accordingly. Under the custom formula I use: =A1>("SheetB!A1"), but it doesn't seem to work. I use the color Green for the > and…
Josh
  • 767
  • 2
  • 6
  • 11
62
votes
1 answer

Reference a cell using formula?

In Google Sheets I need to reference a cell by (column, row) in a way where I can replace "row" with a formula to find a specific row. In other words I can't use a reference like "A7" because my row is determined by a formula. Here is the formula…
user3143232
  • 621
  • 1
  • 5
  • 3
60
votes
7 answers

Google spreadsheet API, 400 error bad request : unable to parse range

I am trying to access Google spreadsheets using a spreadsheet example. When I run the example code it worked fine. I just change the SpreadsheetId and range. It started giving me: Exception in thread "main"…
Hemant Yadav
  • 601
  • 1
  • 5
  • 4
60
votes
3 answers

How to get the price of a stock at a particular date from Google Finance?

I want to get the price of a particular stock at a particular date in Google Sheets using the Google Finance forumula. I tried this formula: =GOOGLEFINANCE("GOOG","price",12/13/2013) But it didn't work. How can this be done?
guagay_wk
  • 26,337
  • 54
  • 186
  • 295
59
votes
4 answers

Google Sheets: How to replace text in column header?

I have a query like this =QUERY(B2:C9; "select (C * 100 / B) - 100") in my Google Sheets. What is displayed as a column header is: difference(quotient(product(100.0()))100.0()). I want to put a human readable description there instead. How can I…
Guillaume Perrot
  • 4,278
  • 3
  • 27
  • 37
58
votes
10 answers

How to utilize date add function in Google spreadsheet?

I believe the issue I am having now should be much easier in MS Excel. However, since my company uses Google Spreadsheet so I have to figure out a way. Basically, I have a cell that contains a date value like "12/19/11", and I have another cell…
Kevin
  • 6,711
  • 16
  • 60
  • 107
58
votes
5 answers

Script to Change Row Color when a cell changes text

I have a Google spreadsheet where I keep a list of bugs and whenever I fix a bug I change the status from "Not Started" to "Complete". I want to write a script for the Google Docs spreadsheet such that whenever I change the status to "Complete" the…
MadSeb
  • 7,958
  • 21
  • 80
  • 121
1 2 3
…
99 100