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

learn more… | top users | synonyms (2)

1
vote
3answers
5k views

How to auto increment a field in Google Spreadsheet

In my spreadsheet I have a column called S+ No. How to auto increment this for each row, like: S1,S2,S3,S4 In short how to make it dynamic.
1
vote
1answer
2k views

How can I apply a script to only one specific sheet within a spreadsheet

I have a spreadsheet with two sheets called Robin and Lucy. I've made/found/mangled a script to sort the data on the sheet each time I add some data to column A function onEdit(event){ var sheet = ...
1
vote
1answer
282 views

Google Docs time stamp?

The following code was almost exactly what I was looking for to timestamp the adjacent cell after being edited. However, I need to incorporate the currnet date as well as time. The code below gives a ...
1
vote
1answer
7k views

How do I use the INDEX or OFFSET functions in Google Spreadsheets?

I have my spreadsheet set up as follows: first 10 columns full of descriptive data followed by 24 columns of month data (2 columns per month). The last 24 columns are static and the values are ...
1
vote
1answer
1k views

Google spreadsheet sorting messes up cell comments

I'm running into this weird problem with Google spreadsheets: if the cells have comments on them and I sort the spreadsheet, the rows sort but the comments stay in the same position, and don't move ...
1
vote
1answer
119 views

Stop lines in GDocs chart overlapping/obscuring other lines

How can I stop lines in GDocs chart overlapping/obscuring other lines? See for example the green line in the graph below, which starts up high, butthen goes behind other lines such as the yellow one. ...
1
vote
1answer
114 views

Javascript string() function in Google AdWords

Is there a simple way to implement the Javascript string() function in the basic Google AdWords interface? I want to pass some variables returned from a campaign management script to a spreadsheet ...
1
vote
1answer
1k views

How can I prepare a Sparkline graph (line/bar) in a Google spreadsheet?

How can I prepare a SPARKLINE graph (line/bar) in a google spreadsheet? With the google drive help it doesn't work for me. Reference: Google Drive Help
1
vote
1answer
2k views

How do you add a date created to Google Forms / Google Spreadsheets

We have a Google Spreadsheet It has a few columns Issue Raised By Date Raised We've turned this into a Google Forms which we want anyone in the business to be able to answer. The problem is ...
1
vote
1answer
1k views

Updating data validation for a group of cells

I have a spreadsheet where a particular group of cells has defined drop down values to choose from, using data validation rules to get the list from a seperate sheet. How do I update all of the cells ...
1
vote
1answer
614 views

Embed only cells from Google Spreadsheet and not application header

When I embed a Google spreadsheet, I get cells in my page (and this is good). But I get also the application header with toolbars (and this is bad for me). See a test here: ...
1
vote
1answer
831 views

Avoiding duplicates when adding cells in column one by one in Google Spreadsheets

How can I have a kind warning if I am adding a text to the cell which was already added somewhere in previous cells of the same column?
1
vote
1answer
1k views

Nested IF statement on Google Spreadsheet, second part same as the first

I have a spreadsheet for my budget. Payments are either drawn from my bank or my Amex card and then my Amex card is drawn from my bank as well. So I add up all my monthly total like this: ...
1
vote
2answers
1k views

Editable Google Spreadsheet on Google site - assign username

So here is what I am trying to do: Have a Google site that has an embedded editable Google Spreadsheet on that allows end users (if they have been given permission) to edit the spreadsheet. Here is ...
1
vote
1answer
2k views

How can I make some data on a Google Spreadsheet auto-sorting using onOpen?

I was working with @W0lf here but I am unable to comment/continue the conversation anymore. I am trying to use autosort function onOpen but is not working: function onOpen(event) { var ss = ...
1
vote
1answer
1k views

Bring data from Google Spreadsheet to Google Documents

Is there anyway to read the data from a Google Spreadsheet (which I owh) to another Google Document, something like specifying a data source in MS Word?
1
vote
1answer
776 views

Duplicating a set of columns with various widths within the same spreadsheet

I'm trying to duplicate a set of 5 columns multiple times on the same sheet. For example, column A is wide, columns B & E are medium width and Columns C & D are narrow. I want to duplicate ...
1
vote
1answer
348 views

How can I automate a series of actions, after receiving an email in GMail?

Here's what I want to do... When an email with a known subject, or from a known source, is viewed I want a button that will: Add data from the email (could just regex parse) to a new row in the ...
1
vote
2answers
555 views

Google spreadsheet flextime

Being a (productive) geek, I want to keep track of the trend of my flextime. I keep a personal document of my hours worked in a Google Spreadsheet, having one column for hours worked every day. By ...
1
vote
1answer
5k views

Applying two filter functions in Google Spreadsheets

I am trying to apply two filter functions in Google Spreadsheets: Column (A) contains new data Column (B) contains filter data, i.e. cells with text that should not be included in column C Column (C) ...
1
vote
1answer
354 views

Google Docs Spreadsheet sections

I'm using Google Spreadsheets. I want to be able to have several different sections all on one spreadsheet: an income section with totals there an expense section with totals in it etc. How do ...
1
vote
1answer
87 views

How to dash a whole row if a cell is OK?

I have a cell that can be OK, ERROR or WARNING. When it is changed to OK I want to modify the whole row in some way. I was thinking about to dash all cells. Is it possible? Or is there something I ...
1
vote
1answer
700 views

How can I charts based on day and month using dates in google spreadsheet?

I have a Google spreadsheet that has values like 1/1/10 0 1/2/10 43 1/3/10 17 etc. I want to be able to visualize the data on a day-by-day basis and on a month by month basis. I managed to create ...
1
vote
0answers
28 views

How to add a line break in a custom date format in Google Spreadsheets?

Google Spreadsheets give us a tool to create and use a customized data format for date values. While the tool allows us to use arbitrary characters, like slashes and commas between the date fields ...
1
vote
1answer
22 views

Print text associated with a value

I have a Google Sheet that collects data and analyses any trends. What I'm trying to do is figure out how to output the text value associated with the numerical value. Example: Apples 7 ...
1
vote
0answers
18 views

ImportRange with preserved formatting in Google Sheets

I am importing a certain range of data from a master sheet and would like to maintain the formatting for those cells. Cell reference with colour formatting This would be the perfect option, except I ...
1
vote
0answers
45 views

Cell-dependent cell ranges in data validation in Google Sheets

Regarding Google Sheets -- is there any way to have cell-ranges in data validation depend on the cell itself. For instance, I want G7 to accept input only from E7 and F7. Similarly, I want G8 to ...
1
vote
0answers
26 views

Filter by Date Range in Google Spreadsheets

The filtering in Google Spreadsheets seems to only offer text based solutions. Is there any ability to filter cells by date ranges like any of the following: Today Yesterday Last Week Since ...
1
vote
0answers
33 views

Filter from multiple sheets into graph using Google Spreadsheets

Context I've got a big travel expenses spreadsheet. In it, I have daily entries for different expenses labelled with things like Date Currency Ammount Exchange Rate Amount in £ (my native ...
1
vote
0answers
16 views

Jagged array from Google Spreadsheet range

The range in my Google Spreadsheet looks like this: | A | B | C | D | E | F | ...
1
vote
1answer
16 views

Reverse (or repopulate) a Google Docs Form

I created a Google Docs form that collects (mostly) biographical and contact data from users. Now I would like to reverse the process and repopulate the form with each user's input, one page per ...
1
vote
0answers
24 views

Reference an entire sheet in Google Sheets

Is there a way to reference an entire spreadsheet with a cell reference in Google Sheets? Say I wanted to find out how many cells in Sheet1 were non empty. In sheet two I'd want to do something like ...
1
vote
1answer
32 views

Google sheet multiple column lookup

I have a table that looks like this: A | B | C ---|--------|--- 10 | Item x | 1 10 | Item y | 1 10 | Total | 2 20 | Item x | 4 20 | Item z | 5 20 | Total | 9 Now I would like to perform a ...
1
vote
1answer
40 views

Calling IMPORTRANGE inside of REGEXMATCH function?

I'm interested to know whether (and how) can i call IMPORTRANGE function inside of REGEXMATCH function. I want to get root domains from list of URL's and use REGEXMATCH to do this but want to get ...
1
vote
2answers
41 views

How to sort cells with text and numbers correctly

This is my example list: example file As you can see I am sorting all unique elements in row B. But the "Sprint 9" gets sorted as the highest value, not the lowest. I really have no clue how I can ...
1
vote
0answers
33 views

Import a CSV file from a URL to Google Docs and update it periodically

I'm looking to pull data from a CSV file that gets update regularly into a Google spreadsheet that I can format the way I want. I know how to import the CSV as a one time thing but I don't know how to ...
1
vote
0answers
28 views

How to change default row height of a migrated Google Spreadsheet

The old Google Spreadsheet has a default row height of 17. The new Google Sheets has a default row height of 21. After a spreadsheet is auto-migrated by Google to the new Google Sheets, the original ...
1
vote
0answers
32 views

function to verify all cells in active row

I have a function which is triggered onEdit to enter an ID in new table entries: function IdGenerator(e) { // Get the active sheet var sheet = SpreadsheetApp.getActiveSheet(); // Get the ...
1
vote
1answer
41 views

How do I get a weekday 12 weeks ago using in a Google script?

I'm having the following formula: =TODAY()-WEEKDAY(TODAY(),3)-84 and this gives me Monday, 12 weeks ago. I'm working on this code, but it gives me only a certain days back var twelweWeekAgo = new ...
1
vote
0answers
17 views

Help me check if this URL is ok to query with Google Chart

For the last couple of hours been trying to get this work. I setup a demo sheet with just two colums: Name Value A 22 B 33 C 44 D 12 And want to plot it. I put this URL into ...
1
vote
1answer
62 views

Auto increment ID in Google Forms

I am designing a Google Form for managing a congress registration process. I need to have a unique ID number for each registration and I found an answear here: Can I add an autoincrement field to a ...
1
vote
2answers
26 views

Google Spreadsheets: Using conditional formatting from date in another cell

I have two columns side by side containing dates. The dates in column A are based on when a project is due. The dates in column B are based on when a project is completed. What I want to do is show ...
1
vote
0answers
59 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
96 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
0answers
20 views

Is there a way to stop Google sheets from telling me to use Ctrl C?

I know the keyboard shortcuts for cut, copy and paste, and I use them frequently. But there are times when using the mouse is much more convenient. Especially if I want to copy a whole row, I prefer ...
1
vote
2answers
27 views

How to add adjacent cells in Google Sheets

I am looking for a way to add up cells under a name. For instance, I am trying to tally all of the assists the character "Annie" got in a few different games of League of Legends. I am looking for ...
1
vote
1answer
17 views

Subclauses in formulas?

Quite often I end up including the same calculation in a formula twice, like this: =if(iserror(vlookup("lookup",SheetX!A2:X350,5,false)),"",vlookup("lookup",SheetX!A2:X350,5,false)) I'd like to ...
1
vote
2answers
34 views

How do I get the data in a cell of the last column of a row?

I'm using Google Sheets. Every week I add a column with new data, and I need the data from the latest column copied into another sheet. The formula needs to pull the cell of the last column in the ...
1
vote
0answers
26 views

How can one serve a single google spreadsheet cell content to a web page?

I can embed a Google Docs/Spreadsheet's whole tab/table on a web page. Is there a simple way to publish the (unformated) content of a single cell?
1
vote
0answers
59 views

If condition won't work Google Spreadsheets [duplicate]

I have this formula and it doesn't work: =IF(A1="oui","1",if(A1="non","0","")) I asked a couple of friends to test it, and oddly, it works fine with them! What did I do wrong? And how do I fix it?