A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

-3
votes
1answer
28 views

Why is this conditional formatting still highlighting cells that don't meet the condition?

I'm trying to apply Conditional formatting applying to column K (range K2:K1001). What I want to do is to highlight each cell in the column if it's date value (e.g., "6/1/2016") is less than a date in ...
9
votes
2answers
5k views

In Google Spreadsheet, finding what formulas reference a given value

I would like to find out which cells have formula dependencies in a large spreadsheet. I am looking for a way to do something like OpenOffice Tools>Detective>Trace dependents and Edit>Find ...
3
votes
2answers
5k views

Pull CSV data from URL to Google Spreadsheet

I have a site which allows authenticated users (read: in-house users) to download certain data from the site in CSV format, e.g. http://example.com/activities.csv . Can I create a Google Spreadsheet ...
15
votes
6answers
102k views

Checkbox function to automatically add to total

I'm doing my club's accounting in a Google spreadsheet. I am looking for a way to put a checkbox at the beginning of a line in order to tick off the members who paid their dues and then have a ...
9
votes
2answers
366 views

Google Sheets - Script to change individual text color within a cell

I have a Google spreadsheet with hundreds of employee names for a schedule. Each cell has multiple names within that cell that look like this: First Last First Last First Last First Last First Last ...
9
votes
2answers
17k views

How do I create a box plot chart in Google spreadsheet?

I want to create a simple boxplot from 1D data, which doesn't seem to be supported by the default Insert → Charts. There is a histogram gadget but I haven't seen any boxplot gadgets. Do I have ...
-1
votes
2answers
1k views

How to get the Elapsed time of two cells hh:mm in Google Spreadsheet

In columns K and L I have time stamps. Let's say 03:30 is in column K and 04:30 is in column L. I want the total elapsed amount of time in hh:mm in column P. I have tried the average function, ...
5
votes
1answer
3k views

Google Spreadsheet Sort Two Columns

So I've found the following answer in regards to auto-sorting by one column, which works great How can I make some data on a Google Spreadsheet auto-sorting? However, my use case requires the sorting ...
-1
votes
0answers
16 views

SUMIF formula in Google Sheets

I am having trouble with a formula and maybe you can help me. I have created a Google Form where I am capturing when and how much my baby eats, among other things. I have a Sheets responses file ...
1
vote
1answer
198 views

How can I personalize HTML emails using Mail Merge and Google Spreadsheets?

I'm attempting to personalize an email that uses HTML coding. Basically, I've made an HTML template that I want to have say the name of a person within the HTML code as well as the subject line of the ...
1
vote
0answers
23 views

Replacing last edited response with the old one in another tab [duplicate]

You can ignore column D, E, F and G cause those are just alphabetically ordered. So basically I want to draw the names of the responses on the responsesheet to this tab (see screenshot), which is what ...
1
vote
1answer
43 views

How to auto generate the date a URL was last updated?

I'm trying to find out if it's possible to fetch the date a URL was last updated. So column A I'd have the URL and column B the data would auto generate. I'm guessing this would be done using some ...
-3
votes
0answers
26 views

google spreadhseets [on hold]

I am building a spreadsheet for sports analysis and Im stuck on a function that I can't seem to complete. I want to have a tab that has a drop down menu, in that drop down menu if vs. left is ...
2
votes
3answers
4k views

How do I create a multiple choice question in Google Forms based on spreadsheet data?

I am making a Google Form for evaluations. I will have a multiple choice question in there and the available options have to come from a list of items, say on a spreadsheet or a text document. I have ...
1
vote
2answers
23 views

How can I see Google spreadsheets in Myanmar font?

I want to know whether it is possible to see Google spreadsheet in Myanmar font. Because in my Ubuntu operating system I am not able to see the spreadsheet text in Myanmar font which show only boxes. ...
1
vote
1answer
32 views

How to get most frequent terms from a set of lists?

I have a list on Google Spreadsheets. Each line is a mission, and one of the columns indicates who I've worked together on the mission (which is between 1 and 3 people). The format is "Aquaman, ...
0
votes
1answer
126 views

Conditional deleting in Google Spreadsheets

I'm using two sheets, one with the main values and reference datas. The second sheet is for price calculation. I use =Filter to get the values from sheet one where Sheet1!E2:E <> 1. How can I ...
1
vote
1answer
68 views

How to embed an image inside a Google Document comment?

I add comments all the time to Google Documents, however i would like to embed images inside Google Document comments (similar to how you can add images to facebook comments). Is this possible and if ...
0
votes
1answer
17 views

Google Sheets sharing setting, “Specific people can access” but can't see each other

For Google sheets sharing setting, I have it on "Specific people can access", but the people can see who has access to that sheet. Is there anyway to make the setting so that the specific people can't ...
1
vote
1answer
15 views

Script to delete a dynamic range of rows

I'm looking for a script that allows me to delete a dynamic range of rows. Basically I have a script that adds rows to the bottom of a list but I want another script that will delete all rows in the ...
0
votes
1answer
27 views

Copy a row and add to the bottom of a list

I'm looking for script that will allow me to copy a specific row and paste it to the bottom of a list. The bottom of the list will not be the last row on the sheet. Using VBA I would have used an ...
0
votes
0answers
15 views

Google Sheets - One Button to hide/unhide columns [duplicate]

I'm new to google sheets and I'm struggling to create a button within the sheet that is linked to custom script that hides or unhides Columns M:Q whenever the button is selected. Not a menu button. ...
0
votes
1answer
19 views

Using Google URL Shortener API in Google Sheets for bulk URL shortening

Recently come across similar issue to this question, where I want to add use the Google URL shortener API in Google Sheets for bulk URL shortening. However, I keep running into this error: Rate ...
1
vote
1answer
30 views

How to share different tabs in the same Google Sheet with different people/groups

I usually create Google Sheets that have several tabs. Usually the audience of each tab should be completely separate. However it makes sense to keep these tabs in the same sheet since I like to have ...
3
votes
1answer
81 views

Problem: Script does not trigger on FormSubmit: Removing Duplicates in Google Sheet of Form Responses based on a column

I am trying to remove older duplicate form responses based on a column using the following code. The credit for the code goes to: ...
1
vote
1answer
22 views

Reference one of my own spreadsheets, within another, by its name

I would like to reference a spreadsheet that is within my own Google Docs, owned by me. The catch is, the spreadsheet changes. It will be a different spreadsheet ever day, with the date as it's ...
2
votes
1answer
29 views

Selecting a single column from all rows for chart range

I want to specify a certain column from all rows (omitting the first few) to be the range for my chart. Currently I input this to specify the range: L6:L (note: this automatically changes to L6:L20 ...
2
votes
1answer
21 views

Reverse lookup from Google spreadsheet key

I can't figure out where the data in an IMPORTRANGE function is coming from. How can I use the spreadsheet key to find the the original spreadsheet IMPORTRANGE is pulling from?
2
votes
1answer
67 views

Change logs for Google Spreadsheets

We've got a large shared spreadsheet which is a chart showing user permissions. It's writable by several people. Although Google Drive allows you to roll back the changes simply its interface isn't ...
1
vote
0answers
35 views

How to embed Google Sheet into Google Document and keep the Google Sheet format

How do I embed a Google Sheet into a google document and keep the Google Sheet format in the document? I have got this far with the script but it's not working function updateData() { var ssUrl = ...
5
votes
3answers
12k views

Google Spreadsheets: Get column number by referencing column title

In Google Spreadsheets I have a number of columns that I have given title to in Row 1. Using a formula, I want to return the column number (i.e. 5, 6, 7, ...) for the column that has a specific ...
1
vote
1answer
33 views

How can I automatically set “sheets last updated” in a cell on Google Docs Spreadsheets?

Simple task!.. output a date and time every time something in the sheet is edited! function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets("Data"); var ...
1
vote
1answer
62 views

How does Google know that I am not a human?

Over on Stack Overflow I asked a question regarding accessing Google Spreadsheets with CURL, which doesn't seem to be possible any more, even when setting a valid user agent string. My question ...
0
votes
0answers
43 views

Can I filter or query an importrange in a single formula without importing the range multiple times or using a 2nd worksheet?

Title sums it up, I want to be able to filter an importrange in a single formula, can this be done without importing the range multiple timer in the filter? I want to do this without using a 2nd ...
4
votes
2answers
3k views

Google Sheets Function to get a shortened URL (from Bit.ly or goo.gl etc.)

I have a Google spreadsheet that builds Google Analytics links. I'd like to shorten those links. I tried the Bit.ly example but even their example does not work. How can I do this?
2
votes
2answers
262 views

onEdit only updates top row after pasting (editing) multiple rows

I'm using a Google Spreadsheet and have a script that updates the "last email" column with a date stamp when an employee enters an email address into the prior column. The problem occurs when my ...
3
votes
3answers
15k views

Google Scripts : How to distinguish Empty cells from Zero

I want to count a number of specific responses from a form where the response can be left empty, zero or any other number 'd'. The following code: var data = sheet.getRange("B2:L").getValues(); var ...
2
votes
1answer
1k views

How to make Google Sheet jump to today's row when opened?

I have a sheet that has many rows to put data. Since there are too many rows, I want to have the cursor jump to a particular row when the sheet is opened.
7
votes
2answers
3k views

Can I use column headers in a =QUERY?

Reading the documentation for the =QUERY function1, 2, 3, some of it seems to imply that I should be able to use column headers directly in my query. For example, the =QUERY function takes a third ...
4
votes
3answers
7k views

How can I make some data Autosort in a spreadsheet on edit?

I am working with a simple spreadsheet that has name/lastnames in it. I need for the spreadsheet to automatically sort the entry alphabetically by the last name (column B). I have used the following ...
8
votes
5answers
17k views

How can I refer to two different columns in a COUNTIF function?

I'll try my best to explain. Say I've got a sheet that looks like this (in this contrived example, let's say I'm sending marketing emails to various people, I know their ages and zip codes, and I want ...
0
votes
1answer
37 views

Query formula limitations Google Sheets?

I'm trying to run a query (importrange) formula in a new Google sheet. The data I'm trying to load is from another sheet. The source sheet contains 6000 rows and 12 columns. The formula I tried was: ...
-1
votes
0answers
18 views

Stocks Valuation google spreadsheet

I'm a bit of a dummy as far as spreadsheets go, so hoping someone can help with my dumb questions. I want to have a spreadsheet where I can list all my mid to large caps with the market cap in the ...
3
votes
1answer
36 views

IMPORTHTML 'could not fetch URL' but used to be able to

I made a spreadsheet, a part of which uses the IMPORTHTML function to get data from this webpage. For the first couple of weeks that it ran, it worked pretty great, getting all the data from the table ...
3
votes
2answers
63 views

Coloring cells according to the value to which its content is closest

I am trying to make a guide for my dragons on a game I play but I'm not quite sure how to code what I am trying to do. Link ex. if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its row ...
-1
votes
2answers
34 views

Fetching JSON in Google Sheets (ImportJSONAdvanced not defined?)

Currently working on fetching data from an API with Basic Authentication. Through this script: https://gist.github.com/anemitz/8cc6ef6c41ea4cefe51a13fbd6d8676a function ...
1
vote
2answers
28 views

How can I sort in Google Sheets by cells containing section (or version) numbers?

I'm indexing a document with sections and sub-sections. I want to have one column representing the section-number and be able to sort by this column. However when the sub-section gets higher than 10, ...
1
vote
1answer
24 views

Compute the total of every row with arrayformula

In my spreadsheet, I need to add up each individual row. It does that if I drag the formula down in Y, but then when a new row is added to the data from a Google form, it won't copy the formula. So I ...
17
votes
7answers
18k views

How to pass a range into a custom function in Google Spreadsheets?

I want to create a function which takes in a range... something like: function myFunction(range) { var firstColumn = range.getColumn(); // loop over the range } A cell would reference it using: ...
2
votes
2answers
500 views

Preventing concurrent editing of a cell in Google Spreadsheet?

We often have the case of two people overwriting each other's changes by writing on the same cell at the same time. Is there a way to make it so that cells can't be written by more than one person at ...