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

learn more… | top users | synonyms (2)

1
vote
1answer
4k views

How can I import Google Calendar events into a Google Spreadsheet?

I just need rows with name, date/time, and location. Ideally it should be done automatically every n hours or at every event added. I found a script solution on Stack Overflow, but is there any ...
1
vote
1answer
1k views

Selecting a sequence of sheets in Google Docs / Spreadsheets

I have a Google spreadsheet where each sheet is a student group, say, Group1, Group2, Group3 etc. Each sheet is organized identically. I would like to summarize some information from all sheets into ...
1
vote
1answer
671 views

Combining Stuff in Google Spreadsheet

I want to accomplish the following: string = TEST; cell reference = B2, value = ING; RESULT = TESTING Which of the following options are the best to use: CONCAT: =CONCAT("TEST",B2) & ...
1
vote
2answers
132 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
2answers
2k views

Return an Offset Cell Value, Where Last Occurrence in Range is Matched

I have a worksheet like this: || A | B | C | ---++--------+-------+-----| ---++--------+-------+-----| 1 || 1 | a | | 2 || 2 | b | | 3 || 3 | X ...
1
vote
1answer
2k views

What's the biggest csv file you can import into a Google Docs Spreadsheet?

I'm trying to upload/import a 7mb csv file to Google Docs without much luck; What's the largest file size that you can import?
1
vote
1answer
11 views

Time and Date Edit by GMT Zone

I need to edit the date and time format in a Google spreadsheet. The spreadsheet has 300 rows with the following format in text 2016-04-21 21:28:00 +0000. Using the find and replace feature I would ...
1
vote
1answer
36 views

Formula to use to return a value of 1 for several cells that meet a certain crtieria

Here's the spreadsheet I need help with: https://docs.google.com/spreadsheets/d/1DGYgprXe7V7WH2HII0hvQDWXl1SaG_a4MUZ5HDGhSq8/edit#gid=258610801 I basically need cell B21 in the "Report" worksheet to ...
1
vote
2answers
19 views

How to concatenate date value with a string?

I need to create the following value: // "ABC" + current date + some random number ABC20160405123 I do as follows: =CONCATENATE("ABC", NOW(), 123); But instead of getting expected value I get ...
1
vote
1answer
20 views

Show false if field empty, function results if not [closed]

I am trying to fix the following condition: =if(AND($L$1>=F9,F9="");"exsteeded";"") This will actually do, if a certain date is over the current date, the exceeded will show. but it have to ...
1
vote
1answer
34 views

How to write an IF-formula with multiple OR/AND arguments to detect duplicates?

Update3: Thanks again for you patience and kindness! Unfortunately it's still not working: "Formula error" Update2: Changing all commas (,) to semicolons (;) gives me "Could not find a matrix ...
1
vote
1answer
30 views

How can I extract the price using importxml from an AngularJS site?

I need to extract the price from this website using importXML on Google Spreadsheets. Here is a product https://en-sa.wadi.com/apple-iphone-6s-16-gb-4g-rose-gold-with-facetime-61616.html The issue ...
1
vote
1answer
30 views

Search text in all columns of multiple sheets

Given many texts to look for, I want to search all my sheets in all columns if there's at least one partial case insensitive match then return true (or 1 or the sheet's name or whatever) and false ...
1
vote
2answers
477 views

Extract the link text and URL from a hyperlinked cell

Suppose I have a hyperlink in cell A1: =hyperlink("stackexchange.com", "Stack Exchange") Elsewhere in the sheet, I'd like to have formulas that get the link text and URL from A1, separately. I found ...
1
vote
1answer
138 views

How to retrieve email address of current user?

In Google Spreadsheet I am having the following issue: I am using Session.getActiveUser().getUserLoginId() to fetch the current user's email id but other than creator of the spreadsheet it is ...
1
vote
2answers
54 views

How can I highlight duplicate cells?

I'd like a formula that highlights all duplicate cells, even if they are in different columns. In my example spreadsheet A1 is equal to D15, the formula should highlight both, and so on with all ...
1
vote
2answers
74 views

Sorting Pre-Sorted Google Form Data by Timestamp

I have Google Form checkbox data that I am separating out into another table so that each checkbox selection generates its own line. I received some help in pulling the data over, and the resulting ...
1
vote
1answer
35 views

Conditional formatting based on multiple cells

I'm trying to figure out how to write a formula for Google Spreadsheet where: If F3 Contains Data and G3 is more than 0 then cell J3 is Highlighted in Green Can anyone help me out with this?
1
vote
2answers
55 views

Formula result depending on the week of the day

I've got a bunch of cells that have formulas to display the day of the week according to the date above it. (I'm using Google Sheets by the way) I want the cell underneath the day of the week that ...
1
vote
1answer
200 views

Sorting alphabetically (A-Z) ignoring beginning “A”, “An” and “The” using a script

I have a Movie list using Google Sheets that I would like do some sorting with using a script rather than a formula. Column A is the movie Title. I would like to be able to sort the movie titles ...
1
vote
1answer
32 views

Get unique rows and their count in Spreadsheet

I have a sheet as below, | A | B | C | ------------- |abc|123|Yes| |jkl|123|No | |abc|123|Yes| |abc|567|Yes| I want to get the output like, | A | B | C |Count| ------------------- |abc|123|Yes| 2 ...
1
vote
1answer
29 views

How do I change the year in Google spreadsheets

I'm copying and pasting things over from an old spreadsheet and the original dates where marked like '25/4' so dd/mm but I never included the year, well the problem is these where dated in 2014 but ...
1
vote
1answer
113 views

How to create an automatically updating Google spreadsheet with information from Steam?

How would I create an automatically updating Google spreadsheet containing information about Recent Activity in Steam? Table must look like here: But the hours column must automatically updating ...
1
vote
1answer
61 views

If time is before 17.00 use 17.00 OR if time is after 17.00 use value in cell

Headline says it all. I'm trying to calculate worktime in minutes. Ex: https://docs.google.com/spreadsheets/d/1GmGyi4kQf8I2qvEJjfG3OZvm_n_kBbsw_doTi9J0KCk/edit?usp=sharing I have to state when ...
1
vote
2answers
33 views

Show number in a cell - based on a value from another cell

I need help with the question on the picture. Can anyone make a formula for me? Thanks
1
vote
1answer
319 views

Add timestamp to two different columns in Google Spreadsheets

Need help in adding another function using the below script. I would like to have a timestamp "start" and "end" on two different columns, whenever the other two separate columns are edited. Based on ...
1
vote
1answer
595 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
1answer
152 views

Google Sheets - Auto update table and auto add new data entries

I have a table in wich raw data is entered manually, with info about clients and the requests they made. And then I have this table in which I'm trying to analyze a bit the raw data (# of requests ...
1
vote
1answer
248 views

Can I use ArrayFormula with CountBlank or IsBlank if so how?

=IF( OR(COUNTBLANK($F3)>0, COUNTBLANK($G3)>0, COUNTBLANK($H3)>0, COUNTBLANK($I3)>0), "Missing Values", $E3+(index(LSI_Index!$B$4:$B$40,MATCH(VLOOKUP($G3, ...
1
vote
1answer
2k views

How to skip blank cells in Google Spreadsheet/Excel when copying a range from source to destination by Formula

I have three levels of hierarchical data in Google Spreadsheet that looks something like this: As data is being entered in here, I want it collect in three separate sheets like this: And The ...
1
vote
3answers
7k views

How can I replace blank cells with zeros in Google Spreadsheet?

I have a spreadsheet where someone else entered the data. They left blank cells instead of recording 0 values. How can I replace the blank cells with 0s? I tried find and replace with regular ...
1
vote
1answer
2k 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
2answers
221 views

Calculate time values in a single Google Spreadsheet cell

Is there a way to add two times, or durations, in one line in a Google Spreadsheet? Something like: =1:14:25+0:17:12 I know that I can use the time command in-line, and that I can add times from ...
1
vote
1answer
600 views

Menu option Insert -> Script… is missing in my Spreadsheet, how do I fix it? [duplicate]

I've 2 documents, in 1st one I was able to add 'Script...' (as suggested here) from the Insert menu, but on the 2nd one and all new which I'm creating I don't have this option present. Why this ...
1
vote
1answer
525 views

How do I import text file as-is to Google spreadsheet?

I'm importing a text file with some columns being 000000, 0000, 000000000. Google Docs converts them all to 0 - treating them as numbers. How do I make the columns contains exactly the string that was ...
1
vote
1answer
56 views

Add a sheet from a template

Is there a way to add a new sheet to a spreadsheet from a template? If I'm using a template for time, I'd like to add a new sheet from the same template every two weeks.
1
vote
2answers
791 views

How can I easily fill a column in Google Spreadsheet with random values from a list?

I need to create some data for a simulation. So I wanted to take a spreadsheet, and for each column, fill it with random data falling within a domain. A uniform distribution is good enough. For a ...
1
vote
1answer
136 views

Using SUM() and VALUE()

I have a spreadsheet with invoice amounts. Each amount is a link to the given invoice. Can I use sum() and value() (or any other functions) to somehow use the displayed value for sum() (and not the ...
1
vote
2answers
392 views

Google Spreadsheets: Add 1 if there is data in cell

I have a spreadsheet of data which lists the inspection results of schools, in which I have a column that states the result or is empty if that particular school has not yet been inspected. I want ...
1
vote
2answers
4k views

Google Spreadsheet; Function to Search a Sheet for String and Return Value of Next Column

Here is what I have so far. it will: Search for keyword in my other sheet (JSON) When it finds the keyword (modname) it returns the row # The keyword is on the A column. What I want is for a ...
1
vote
1answer
5k views

How can I group a pivot table into weeks or months?

In Microsoft Excel, in a pivot table, you can right click on the dates - while within the pivot table, on a date and select group (from the drop down menu) - then you can choose different groupings, ...
1
vote
1answer
472 views

Sum every 9th row starting from the 4th row in Google spreadsheet

I have a column where there is a number every 9th row. The problem is that my first value starts on 4th row then all the next ones every 9th row. So far I tried this: ...
1
vote
2answers
51 views

Is there a more efficient formula to use than just copying and pasting?

D3 =(B3^3.0)-(B2^3.0) D4 =(B4^3.0)-(B3^3.0) D5 =(B5^3.0)-(B4^3.0) D6 =(B6^3.0)-(B5^3.0) D7 =(B7^3.0)-(B6^3.0) .... I rarely used Excel but since I don't have it installed on my computer I'm using ...
1
vote
1answer
2k views

In Google Script, get the spreadsheet cell calling a custom function

In VBA for Excel, I can get the column of the cell making the call to a custom function like this: ThisCol = Application.Caller.Column What is the equivalent in Google Scripts? I've been trying to ...
1
vote
2answers
113 views

What is the best approach to adding additional costs to items in Google Spreadsheets?

In Google Drive I'm keeping a spreadsheet of everything I spend money on. I have a heading for the cost but alone that's insufficient. For example, if I tip after a meal I really want to have that ...
1
vote
2answers
593 views

display google docs spreadsheet in mobile mode in desktop browser

When viewing a google docs spreadsheet in a mobile browser you have handy filter buttons at the top. How do I display mobile version of the page in a desktop browser? Preferrably Firefox. Edit: I seek ...
1
vote
2answers
318 views

Finding and removing periods in Google Spreadsheet

I'm using a Google Spreadsheet and trying to create a formula to remove a period and anything that comes before it in a cell. For example: idontwantthis.iwantthis I have used this in the past: ...
1
vote
3answers
1k views

Missing sort in Google Spreadsheets menus

I have a Google spreadsheet that has the A column as an ID field (incrementing numbers 1...1000; no formula, just numbers). That column is frozen as is the first row of headers. I sorted by column B ...
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 ...
1
vote
1answer
30 views

Filter View, Text Starts With in Custom Formula

In Google docs (spreadsheet) under Filter View, I am trying to write a custom formula for multiple conditions. For example, in column O, I may have "Jury Room" and also "Jury Rooms" and so I am ...