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

learn more… | top users | synonyms (2)

0
votes
0answers
23 views

Enable smooth scrolling in Google Sheets for large cells

Similar to this question regarding Excel.
3
votes
1answer
125 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: http://www.jacorre.com/tutorial/remove-duplicate-rows-google-...
2
votes
1answer
49 views

How to select multiple, non-consecutive cells in Google Sheets using a list of the cell addresses

I have a very long, comma-separated list of cell address coordinates, shortened here for the sake of brevity: C3,C7,C17,C56. I would like to select these cells. Is this possible to do by pasting the ...
3
votes
2answers
42 views

How to get a cell to show blank if it cannot lookup the search key?

For Example: If cell A1 contains the letter "A", "B" or "C" randomly in it, and in cell B1, lookup A1 (doesn't matter where). If cell A1 DOESN'T contain the letter "A", "B" or "C" in it, I want B1 to ...
3
votes
1answer
92 views

Google sheets calculate the amount to be invested per month to generate an amount desired using compound interest

Let's say I want to generate $100,000 over 5 years at 9% interest compounding yearly. Is there a function or formula that will let me calculate the amount to be invested per month to reach that goal ...
2
votes
1answer
34 views

Present importXML output in reverse order

How to reverse the order of the output of imporxml? For example, =importxml("http://example.com","//p") outputs +---+---------------------+ | 1 | This domain is ... | | 2 | More information... | +--...
1
vote
1answer
54 views

Prevent importXML from spilling text across multiple columns

When importing chat transcript by importXML, e.g., =IMPORTXML("http://chat.stackexchange.com/transcript/7/2015/8/13", "//div[@class='content']") I find that some messages split across several ...
2
votes
1answer
60 views

Is there a way to automatically duplicate a large number of Google Sheets, name them, then copy their URLs to another sheet?

Background: For a genealogy project, I've created a Google sheet where participants enter all details of one ancestor. (FILEXXXX) In another Google sheet (not just another tab/sheet, another FILE) ...
1
vote
1answer
23 views

Conditional moving average: the last two weeks or over 10 data points

I am looking for a formula to generate a moving average of the last two weeks OR the last 10 data points (whichever produces more data points) conditional upon the presence of data in two other ...
0
votes
0answers
9 views

How do I make a custom set of hidden columns in google spreadsheet which I can turn of and on? [duplicate]

So specifcly to hide, not to filter or pivot table. And specifly columns and not rows. I want to put planned and actual columns next to each other under each day date, I want to switch between (...
2
votes
1answer
86 views

How to show instead of hide repeated values in pivot table?

By standard, repeated values in pivot table are only shown once. I made an example sheet to illustrate: This becomes a problem for people who use filters over pivot tables: Notice how Donald's ...
2
votes
1answer
16 views

Set global “export to PDF” settings for Google Drive Sheets

I'm an Aussie. Google Drive is obviously built for USA first. Each time I export a Google Drive Sheet as a PDF, I have to change the page size from Letter to A4, and other such settings. Is there a ...
2
votes
1answer
57 views

How can I retain cell formatting when data is inputted via a Form?

I have a fuel calculation sheet, and I've created a form that I input the amount I've just filled up (as well as total price and my current mileage) using a Form on my phone while at the pump. The ...
1
vote
1answer
21 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
3answers
46 views

How to display ZIP codes in Google Sheets?

Boston has leading zeros for its ZIP codes. Google Sheets is very helpful in removing leading zeros but unfortunately it removes them from Boston ZIP codes. I tried to find a number format for ...
1
vote
1answer
12 views

Google sheets formula to countunique rows with a condition

I have a sheet where I keep track of some expenses. the data is formatted like this: Vendor A | Plumbing | $250 Vendor B | Cleaning | Vendor A | Cleaning | $19 Vendor C | Washing | $20 | ...
1
vote
1answer
35 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 value....
4
votes
2answers
154 views

How to use “indirect” inside a importrange-formula?

I need help to dynamically fetch a value from another Google spreadsheet like this: FILE1 has 0 or 1 values in column G. Now I want to fetch the G column value from FILE1 with a row number specified ...
3
votes
2answers
36 views

How to restrict a Google Script to a certain cell range?

I'm working in an automatic script that shows the last modified dates of some cells within a sheet. So far, I've succeed with the sheet restriction adding an if condition, but I want to restrict this ...
1
vote
1answer
101 views

Import XML data Reloading everytime I open the sheet

I have a problem with my google sheets updating the IMPORTXML data everytime i open my sheet. Does importxml function run everytime I open Google Sheets ? Is there a way of turning off this? I don't ...
1
vote
0answers
36 views

Auomatically lock/protect a range or row based on date

Any one can help how to do a scripts for auto lock / protect range/row by date I have try but this script only lock a cell only ( cell M:M ) a = SpreadsheetApp.getActiveSpreadsheet(); var s = a....
1
vote
0answers
33 views

Google spreadsheets keep keys in sync between sheets

I have a google spreadsheet with a "master" sheet that has a list of rows (first column is a key and other are some data). I want to be able to have more sheets in the doc that would have the same ...
3
votes
1answer
18 views

Sort Column but keep associated value in Google Sheets

I have an Google Sheets spreadsheet that looks something like this: |Team #|Rank| | 100 | 4 | | 200 | 3 | | 234 | 6 | I want to sort the teams by Rank, from best to worst, and then assign ...
2
votes
1answer
38 views

Add email hyperlink to spreadsheet

I have a spreadsheet where I track incoming email questions and outgoing responses. As questions come in, I have a script that appends a row (using .appendRow()) with information in the first few ...
0
votes
1answer
32 views

Exercise breakdown in mm:ss and exercise/second

It's a workout of 11 minutes with As Many Reps As Possible. I have no coding experience and very primitive google functions knowledge. Column B is set 'number' of exercise. Column C is the 'running' ...
2
votes
2answers
88 views

Google Sheets: how to resolve emails to user names

We have a Google Apps account for our organization. When you create a Google Form using your Apps account, there is a helpful option to have the form capture the user's email address when the form is ...
2
votes
1answer
64 views

Can I add two calculated fields to a Google Spreadsheet based on a Google Form?

I am new to Google Scripts and am hoping for some guidance on improving a manual process I complete in Google Sheets. To analyse the data I receive on a Google Form I run, I manually add-in (or copy ...
-1
votes
1answer
29 views

Copy row to another sheet if value entered to create a summary of rows from various sheets

I have several sheets that are price listings. My goal is when customers enter a quantity in column F for the item they want to purchase, the corresponding row with all the info for that item (item #, ...
1
vote
1answer
135 views

Google script match values from a column in one sheet to a column in another sheet

I'm trying to filter two columns in Google Spreadsheets, say A in sheet 1 (which contains work order numbers) and A in sheet 2 (which contains the same strings of work order numbers but they are ...
3
votes
1answer
55 views

What's taking CPU time in a Google Sheet?

I have a complex Google Sheet, and the gray bar on the top right takes a long time to complete. Is there a way to see what the sheet is calculating to pinpoint down what part of the sheet is taking up ...
2
votes
2answers
46 views

How to remove duplicate rows based on one column, retaining the first row for each

I want remove all row with equal date 02/03/2016 1 11 24 7 8 3 22 02/03/2016 1 11 23 7 8 3 22 03/03/2016 1 11 25 7 8 3 22 03/03/2016 1 11 25 8 7 3 22 04/03/...
-2
votes
1answer
33 views

Replace sheet name with gid number in Google Sheets

I want to replace the sheet name with gid number in Google Sheets. =importrange("1anqeokmYsrtsdssdsx9QHOXqoVkgio254O8","Sheet1!K5:M5") How can I replace the Sheet1! with its gid number?
2
votes
1answer
195 views

In Google Spreadsheets, how do I make a cell display a value randomly depending on a percentage in another cell

I want to make a cell display the word, 'YES' and 'NO' where the chance of the cell landing on'YES' would depend on the % in another cell. (E.g. 100% would mean there is a 100% chance on the cell ...
1
vote
0answers
58 views

Is there a way to get 'frozen' rows in an embedded Google Sheet?

I have a 400 row sheet embedded in a web page. I would like the header row to be frozen, as it is in the original sheet. So far I've been unable to discover any parameter to allows this, but also I'...
4
votes
1answer
29 views

How do I get Google Sheets to display lakhs correctly?

I would like Google Sheets to format 1,00,000 as I wrote it, and not as 100,000. How do I do that? I tried the following number formats mentioned I found via Googling, and none of them work: ##,### #...
3
votes
2answers
153 views

Concatenate several columns into one in Google Sheets

I want to concatenate/combine columns A, B and C into column D as displayed. The number of filled entries in A, B and C will vary over time, so absolute cell addresses cannot be used. What formula ...
3
votes
3answers
72 views

Boolean values not calculated by =SUM()

I'm trying to use a Google Spreadsheets template called "multiple person expense sharing template" It seems there is some possible incompatibility with it. I think the template has become ...
3
votes
1answer
52 views

Partial match for strings that are truncated beyond 30 characters

I have two sheets. Sheet one has a list of events in column N: IEM Katowice, and others. However, our system imports event names that are longer than 30 letters (including spaces) with a .. at the end ...
0
votes
1answer
11 views

Is there an easy way to undo changes to a cell, column or sheet within a spreadsheet?

In Google Sheets, I may sometimes edit a cell, then edit another cell, and then decide that I want to undo the first edit. Is that possible? This also goes for rows, columns, sheets with a ...
2
votes
1answer
26 views

How to block automatic row and column insertion in Google Sheets?

While playing around with array formulas, "sometimes" when the result has more rows and columns than the previously available in the sheet, Google Sheets automatically adds the row needed. I said "...
2
votes
1answer
39 views

Can I use the IRR formula without listing out each investment?

Let's say I've invested ₹1 in a mutual fund every month for the last 88 months, with a resulting corpus of ₹236. I want to find the annualised rate of return using the IRR formula. To do so, I have ...
3
votes
1answer
58 views

What's the formula to select a range excluding a sub-range?

I want to say A1:A60 excluding A20:A30. Is there a minus operator for use with ranges, in Google Sheets? I know that you can take the union of two ranges, like {A1:A19;A31:A60}. I would like to know ...
1
vote
1answer
32 views

Trouble renaming a pdf file created in Apps script

I used Google Forms to make a 20-question quiz and added a page to the response spreadsheet that automatically grades the quiz immediately after it's submitted. I have this script in place to ...
2
votes
1answer
43 views

Auto create sheets within a spreadsheet with formatted columns and cells

What I would like to do is be able to run the script and have it create 4 new sheets (within the current sheet I'm in) with the following formatting in each: 1st row frozen with column headings on ...
2
votes
2answers
376 views

How to update Google “My Maps” when Spreadsheet data changes?

In the process of building a Google My Maps map, I found that it's quite handy to import spreadsheets from your Google Drive to use it source for your map layers. As using spreadsheets is pretty much ...
5
votes
1answer
299 views

Lock a Google Sheet after 00:00

I have this situation: every working day I have a Google Sheet where me and my staff enter some data. I want at certain date and time my staff to be blocked from editing that Google Sheet, so only the ...
6
votes
1answer
70 views

How do I concatenate data from multiple columns on one sheet into a single column on another sheet?

I need to take separate columns containing first and last names on one spreadsheet, and combine them into a single column on a different sheet. Right now my formula is =CONCATENATE(IMPORTRANGE("URL", ...
1
vote
2answers
39 views

An arrayformula to find the previous non-empty cell in another column and find the last row to work with

I would like to do something similar to what was solved in the question: An arrayformula to find the previous non-empty cell in another column The problem was: If I have a column "input", I would ...
1
vote
1answer
34 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
46 views

Find in range and go to cell. Trigger On form submit not working!

I am using a script that checks column range B2:B for a specific value contained in a cell of another column (the value in this cell deliberately corresponds to the last value in the column B) and ...