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
34
votes
10 answers

Write an array of values to a range of cells in a spreadsheet

Using Google Apps Script, is there a way to write the values in a Google Spreadsheet array to a range without looping? I am thinking something like the following to put one name each into cells A1:A3: function demoWriteFromArray() { var…
Playing with GAS
  • 565
  • 3
  • 10
  • 18
33
votes
7 answers

You do not have permission to call "UrlFetchApp.fetch"

I am sending http request from script editor in google spreadsheets, but I keep getting the following error message: Google Apps Script: You do not have permission to call UrlFetchApp.fetch. Required permissions:…
Haq Nawaz
  • 412
  • 1
  • 4
  • 12
33
votes
3 answers

A "constant" in google spreadsheet?

Say I have 2 columns: (this is extremely simplified) Data = a number Result = Data * 1.2 I can put B2 = A2*1.2, then drag and drop B2 down... and it fills all the other cells, which is perfect. But can I put this multiplier (1.2) somewhere as…
Aximili
  • 28,626
  • 56
  • 157
  • 216
33
votes
3 answers

Is it possible to automate Google Spreadsheets Scripts (e.g. without an event to trigger them)?

Does anyone know if it's possible to use Google Apps Scripts to run without needed a manual event? Meaning - I would like to set up a spreadsheet to send a form every day at a specified time. I have figured out how to manually send the form by…
AJW
  • 331
  • 1
  • 3
  • 4
33
votes
6 answers

"We're sorry, a server error occurred. Please wait a bit and try again" error when running a function from a custom menu

I have received 341 error notification emails for the below error We're sorry, a server error occurred. Please wait a bit and try again. The notification email only tells about the name of function in which the issue is occurring. The script is…
MA1
  • 2,767
  • 5
  • 35
  • 51
33
votes
4 answers

Custom function throws a "You do not have the permission required to setValue" error

I am trying to set some value to a cell in a Google Spreadsheet: function exampleFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var range1 = sheet.getRange("A1"); var value1 =…
zyrup
  • 691
  • 2
  • 10
  • 18
32
votes
7 answers

How to convert Time into decimal float in Google Sheets using Script?

I want to convert the time HH:MM into H.xx Like I am getting it in this format: Sat Dec 30 00:00:00 GMT+05:21 1899 But this value is 04:29 in cell. I want it to be 4.5 hours to multiply it to hourly rate.
TheOnlyAnil
  • 877
  • 1
  • 15
  • 27
32
votes
4 answers

Spreadsheets get unique names but ignore blank cells

I would like to use spreadsheets to get all unique names from Column A in a table but in the same time I would like blank cells to be ignored. So far I've got this formula that returns all of the unique names from column A but I don't know how to go…
DIzZy
  • 323
  • 1
  • 3
  • 4
32
votes
4 answers

Import JSON data into Google Sheets

I am pulling data from a web service and it is formatted as JSON. I am writing a Google Apps Script for Google Sheets that will populate the data for me. My problem is, I can't seem to get it to parse out. Doing: var dataset =…
joejoeson
  • 1,107
  • 1
  • 10
  • 14
32
votes
2 answers

Make Google Spreadsheet Formula Repeat Infinitely

Okay so I have a Google Form that dumps info into a spreadsheet. On each line I need to have a simple calculation done. The problem is I can't figure out how to get it to repeat a formula on every new line as new lines are added. Yes I know how to…
slister
  • 769
  • 1
  • 13
  • 29
31
votes
3 answers

Changing background color of specific cell on Google sheet

I am working on this script bound to a Google Sheets spreadsheet where I have this function running from a time-driven trigger. I would like to be able to target specific cells on the sheet (if the cell value = "Open") so that I can change the…
Dom
  • 313
  • 1
  • 3
  • 5
31
votes
1 answer

Set cell format with google apps script

It is possible to set number format to a cell with google apps script like cell.setNumberFormat("0.000"); I need to set cell format to a text, so that any date-like values were not automatically converted. How can I do this?
Mike
  • 20,010
  • 25
  • 97
  • 140
31
votes
5 answers

Get date from week number in Google Sheets

If I have week 7 in 2017 what week date is the Monday in that week in Google Sheets?
Pylinux
  • 11,278
  • 4
  • 60
  • 67
31
votes
5 answers

Accessing a (new-style, public) Google sheet as JSON

How can I access the contents of a (new-style) Google sheet a JSON? My aim is to access the values from JavaScript, so I need to be able to download the JSON via HTTP. Example: how can I download the data from this sheet as JSON? I tried to find…
jochen
  • 3,728
  • 2
  • 39
  • 49
31
votes
5 answers

How to force newlines in Google Apps jsdoc descriptions

I can't figure out how in a Google Apps Script to display this correctly. I need it to display new lines in the jsdoc output(e.g. when the function tooltip window comes up in a Spreadheet functions.) I have tried html like however it is just…
masshuu
  • 471
  • 1
  • 4
  • 6
1 2 3
…
99 100