One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

1
vote
1answer
100 views

Linking a URL to text using google script in spreadsheet

I have a URL ready to be followed. I am sending an email using Google script, and in this email I need this link to be there... but I do not want it to be: ...
1
vote
3answers
140 views

Conversion from object to string in google scripts

I'm trying to take information from Google Sheets and use it to send an email with the information given in sheets. I have the email part down, but when i try to editAsText to delete unwanted ...
1
vote
1answer
21 views

Have indirect update row when pasted on new rows

According to this answer, I am fetching data from another sheet over rows: http://webapps.stackexchange.com/a/24478/29203 The problem for me is as I am fetching over rows, the supplied answer does ...
1
vote
1answer
17 views

How to set a formula for a entire column?

I need to apply a simple formula into a entire column, this is to prevent having to keep pasting the formula into said column when i add a row... =IFERROR(D1-C1) is the formula that i want to ...
1
vote
1answer
153 views

How to combine and consolidate rows from multiple sheets into one and sort them?

I've few sheets where rows are populated dynamically via IMPORTXML and ImportJSON with the same columns, but they can't be sorted. So I'd like to generate new sheet which will populate rows from ...
1
vote
1answer
134 views

How do I get a date formatted as YYY-MM-DD from a cell for using in a script?

I have a problem getting a date from a cell in my Google Spreadsheet. I would like to access A3 in the sheet Dates, which holds the date 01/12/2013, and re-use that as variable start-date in the below ...
1
vote
1answer
189 views

IMPORTXML: xpath to get non-distinct parent node data for each child node

Google Spreadsheets has an IMPORTXML function, which I am trying to use to maintain a list of TV episodes, based on the API at http://services.tvrage.com/feeds/episode_list.php?sid=3183. I am trying ...
1
vote
1answer
56 views

Associate cells to imported ones with importrange in Google Sheets

I have the following issue: I have table A with several columns and I use the following function to import some of data to table B: ...
1
vote
1answer
173 views

How to put a dollar symbol before every column and row number in google spreadsheet

I have a big spreadsheet in google docs with many formulas like this: =MIN(C3;C4)/500+4. Now we need to add $ before every cell row/column to make it absolute. So, the formula should look like this: ...
1
vote
1answer
41 views

Using a cell text as sheet reference

Is it possible to use a cell text as a sheet reference in Google sheets. I have a spreadsheet with many sheets and want to reference a sheet name from text in a cell rather than having to put the ...
1
vote
1answer
149 views

Find the nth occurrence of variable, look at adjacent cell and organise in an array

I have a Google spreadsheet with two arrays: The first is a list of ~100 names (in a single column) with the adjacent column being the variable (A,B,C,D,E,F,G,H,I or J). The purpose is to group people ...
1
vote
1answer
43 views

Change background color of a cell when another cell is filled in

Let's say I have a Google Sheet where A1 needs to be filled green when B1 till B7 has been filled in with no matter what; and A1 needs to be red when 1 or more cells from B1 till B7 are not filled in ...
1
vote
1answer
176 views

Google Docs invitation to edit on my Outlook email address

I have got an invitation to edit a Google doc on my outlook.com email address, but every time I click on the link, I get the message “You need permission”. I am signed in to my Gmail account as well ...
1
vote
2answers
565 views

Relative copy conditional formatting that references other worksheet

I have a group of worksheets, one of which has 'default' values while the others should only have values if they differs from the default worksheet. To facilitate this, I want to use conditional ...
1
vote
1answer
26 views

How to insert copied text or images by right click in Google Docs editors

In Google Docs editors, is there a way to allow pasting by right click and selecting paste? Why do they disabled this feature by default? Sometimes it’s faster than pressing ctrl+V if your hand is ...
1
vote
1answer
64 views

how to clean up table data in Google Sheet IMPORTHTML?

I want to import a table of nuclear power output from https://ru.wikipedia.org/wiki/Атомная_энергетика_по_странам. For some reason, the second col has the number duplicated, eg <td><span ...
1
vote
1answer
21 views

Google Spreadsheets Formula

I have a problem creating a formula for my sheets. I want Google Spreadsheets to put either Yes or No in a specific cell depending on the values in other cell. In other words I want it to put Yes when ...
1
vote
1answer
33 views

Add FILTER condition if parameter is present

I have on the top of the page some filtering parameters (see $H$1 below). They may be left blank, and if so, they should not be used. How to use then conditionally in FILTER?? The following don't ...
1
vote
1answer
214 views

ArrayFormula to compute Running Average for groups of rows

Is it possible to write an Array Formula which calculates the running average of the Amount column for each of the Name groups in the following sheet? The rows are sorted by Name. In Column C, I ...
1
vote
1answer
143 views

COUNTIF in Google Sheets that checks 'if' a string is in one column before counting in another column

I'm trying to use the COUNTIF function in Google Sheets. The Goal I want the 'dashboard' sheet to display the count of items in column F submitted to the responses sheet 'if' a specific string ...
1
vote
1answer
727 views

Filter columns in Google Spreadsheets based on fill colour

I've got a long list of items that I need to check. I use a traffic light system to track where I change the cell colour of the cells in the progress column: red = haven't done yellow = in progress ...
1
vote
1answer
42 views

Automatically adjust corresponding cells when using “=query”

I just learned how to use the =query function in Google Sheets, pulling information from one sheet to the next when a specific condition is met. =query('Sheet1'!A:Z, "select B, D, E, F, G, I, L where ...
1
vote
1answer
152 views

A more complicated Countif (Google docs)

I have a simple countif:= Countif(A1:A5,"statement"). And I want it to count the statement n times if it appears n times in my cell A1 (plus of course the number of times it appears on the other ...
1
vote
1answer
50 views

Auto Insert Address on adjacent cell

I am trying to create a spreadsheet which enters address automatically on adjacent cell when I type in the company name. The script will be as I type in company name in cell A1, it will search for ...
1
vote
2answers
43 views

Lock formulas in Google Spreadsheets

I'm creating a table for a friend to input information but he is not very good at computers. I had him work on a table in Excel and locked the page and this worked great but now I want to check his ...
1
vote
1answer
1k views

Copy cell from cell to another sheet if row contains x

I'm new to sheets, I have a sheet with 10 pages. I need to auto fill sheet Report if sheet David contains "1" in column "i". I need to copy cells C14 - C34, J14 - J34 and L14 - L34 only if there is ...
1
vote
1answer
46 views

How to lock formula when adding column in Google Spreadsheets

I’m trying to keep an average of the 5 newest values of a data set: Average($d$1:$h$1). If I add a new column to the left of D for new data, the formula changes to ($e$1:$i$1). How do I prevent the ...
1
vote
1answer
41 views

Lookup using a key from a dropdown cell

I have a dropdown that selects from several text "keys," like so: List of items: Pathetic,Untrained,Novice,Adept,Master,Amazing I'm trying to use these keys to run a lookup on a separate sheet, with ...
1
vote
1answer
46 views

List all records with certain criteria

Help. I just cannot get this going even after 2 hours of searching Google. I have a Google Spreadsheet that has a "List" sheet and an "Outstanding" sheet. I want to see what's outstanding from a ...
1
vote
1answer
34 views

Counting Instances in Google Sheets

At my school, we have a Google Form for recording classroom discipline. The form generates the standard spreadsheet. I want a list of how many times each unique student has received a disciplinary ...
1
vote
1answer
463 views

Show all hidden rows and columns when I open a Google Spreadsheet

I want to un-hide all the hidden rows and columns when I open the spreadsheet. How can I do that for all sheets?
1
vote
1answer
233 views

Google Spreadsheets function to filter by date and count text strings in certain fields

I'm not very experienced with Google Sheets, but I found a function here on Stack Overflow and modified it a bit to do almost everything I need: =COUNT(FILTER('2014 Form Data'!$A:$A,'2014 Form ...
1
vote
1answer
56 views

Search for multiple variables in multiples rows at the same time in Google Sheets/Drive

I'm using Google Sheets (only option, sorry, know this should be a database). Sheet 1: List of 200 employee names, and up to 10 location codes they are frequently assigned to. Sheet 2: List of 50 ...
1
vote
2answers
244 views

How to get a Google spreadsheet cell to display a number as 4 digits, even if it's less than that

This is a formatting issue. Basically, I would like to get all the numbers I enter into a column become 4 digits in the other column ("output column"), with 0's in front as applicable. E.g. when I ...
1
vote
1answer
87 views

Google spreadsheet sort auto update

I have this table, which is a ranking, the values are updated automatically (data gathered from other files). What I want is that every time this values are updated the ranking automatically updates ...
1
vote
1answer
78 views

Have a range refer to a given row even if the row number changes?

I have a table like so: A | B ------------------ 1 || Name | Age 2 || joe | 28 3 || bob | 14 4 || Size | Weight 5 || small| 150 And I have a formula to sort each section by column B: ...
1
vote
1answer
239 views

Import current price from website to spreadsheet

As you can probably gather from the title, the goal is to plug the current price of this product into a spreadsheet. Perhaps I'm over-complicating things, but there must be an easy way to do this. ...
1
vote
1answer
73 views

Lookup/Copy data from different sheets?

I am creating a schedule sheet in Google Spreadsheets that will have a number of different people inputting their own information. I would like the Director to be able to open a single document that ...
1
vote
1answer
272 views

Sum of dynamic number of values derived from fomula

In short my question is this. If I use the SUM function (or some other function) that can take one or more arguments, is there some way I can pass in these arguments as an array which varies over some ...
1
vote
1answer
350 views

How do you refresh a filter view in Google Sheets?

Right now it seems like the easiest way to refresh a filter view in google sheets is to remove the filter view and then apply it again. What is the fastest way to refresh a filter view in Google ...
1
vote
1answer
72 views

How to parse a date and time in the format 2012-12-12T00:00:00+00:00?

I have a CSV file with dates in the following format: 2012-06-20T00:00:00+00:00 Is it possible to parse a date or date and time in this format? The goal is to turn it into a date and time that ...
1
vote
1answer
55 views

Freeze first row of Google spreadsheet when displayed in Google site

When a Google spreadsheet is displayed in a Google site window, the complete table being displayed moves when the displayed window is scrolled. The top row containing headers moves out of view. This ...
1
vote
1answer
253 views

Conversion from Excel to Google Spreadsheet - sum across sheets

I'm importing an Excel spreadsheet into Google Drive. Its a huge spreadsheet, and overall Google spreadsheets has converted everything, except the following: There are a few cells with formulas like ...
1
vote
2answers
236 views

New Google Sheet has different download URL? and errors?

I've been downloading the first sheet old Google Spreadsheets as a TSV file using urls like: https://docs.google.com/feeds/download/spreadsheets/Export?key=ID&exportFormat=tsv&gid=0 After ...
1
vote
1answer
208 views

What's wrong with this Google Apps Script code?

Forewarning: I'm a noob at JavaScript, with varying experience and less knowledge. I don't get why this code fails with TypeError: can't call method "getSheetName"/undefined. (line 7, file "Koodi"). ...
1
vote
1answer
29 views

Auto-Import Master Spreadsheet

I'm fairly new to Google Spreadsheets and completely useless when it comes to functions and formulas. Is there a way to take info from multiple sheets within a workbook and compile them into a ...
1
vote
2answers
4k views

Rename Column in Google Spreadsheets

Does anyone know how to rename a column in google spreadsheets? Seems easy enough in excel. https://productforums.google.com/forum/#!topic/docs/ewnE52j57YU I was hoping that ideally I could see what ...
1
vote
1answer
1k views

ImportHTML spreadsheet function to update every 5 mins

I have a Google Spreadsheet fetching stock exchange data using an ImportHTML function: =ImportHtml("http://www.xxxx.xx.xx/xxxxxxxx/livefeedfull.aspx/?"& year(now()) & month(now()) & ...
1
vote
2answers
92 views

Is it possible to prevent people from entering data into my Google Spreadsheet?

Is it possible that I fix the order of user's entries with a script? For example, the user must fill cell A1 first, only after that he can fill cell A2. Maybe my explanation is better with an ...
1
vote
2answers
60 views

How can I prevent users from reverting to an old version of a Google Spreadsheet?

I would like to allow users to edit a Google Spreadsheet, but not be able to revert them to prior versions. Is this at all possible? USE CASE The spreadsheet we use keeps track of snacks consumed, so ...