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

learn more… | top users | synonyms (2)

1
vote
1answer
83 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
46 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
44 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 ...
-2
votes
1answer
30 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
194 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
37 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 ...
4
votes
1answer
28 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: ##,### ...
2
votes
2answers
123 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
66 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
46 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
22 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
35 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
46 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
27 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
38 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
187 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
62 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
35 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
32 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
39 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 ...
2
votes
1answer
49 views

Periodically updating a URL in the ImportXML command with a script

I am using importxml to extract prices from multiple websites. My main concern is to be able to refresh the prices whenever I want and not be dependent on Google's hourly update. I am using this ...
2
votes
1answer
52 views

Google drive (sheets) shared file - move ownership - different domains

I'm helping my daughter with her project and started an example of how to create a table using google sheets, which I shared with her. She continued on with it but now I want to remove it from my ...
1
vote
1answer
147 views

How can one auto create pre-filled URLs with registered addresses from a roster

We use google forms to send out tests to our students. We have a spreadsheet with rosters where we keep the info of the students (email, birthday, gender, guardian etc) for every class in our school. ...
2
votes
1answer
30 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 ...
0
votes
0answers
49 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 ...
2
votes
1answer
39 views

How to auto format a phone number with an associated hyperlink in Google Sheets

I don't know if this is possible, as its a bit of an edge case, but it would be amazing if this can be accomplished. I have a spreadsheet of contact information, with fax numbers. At work, we send ...
0
votes
1answer
24 views

Google Spreadsheet Query blanks out text cells in the query range

When I perform a query to essentially filter a range of names, and times, any cell that is not a time gets blanked out. Screenshot of example: I'm keeping this post fairly short since the image ...
2
votes
1answer
50 views

Google Spreadsheet Conditional Formatting depending on up to 7 Fields

I'm trying to color a column depending on the row. My table looks like this: the only thing i managed to do was =NOT(ISBLANK(C101:C104)) and set it to orange using the conditional formatting. I ...
3
votes
1answer
24 views

How to edit dates in Google Sheets as ISO 8601?

I know how to make Google Sheets display dates in ISO 8601 format (YYYY-MM-DD). Now, how do I make them not only display, but edit in the same format? See the edit line below: Moreover, if that ...
0
votes
0answers
29 views

Your Time stamp code

I have copied and pasted your code to put a time stamp on a spreadsheet that I am working on and it is working great. Except the day is coming back as "56" for some reason and I don't know why. It ...
2
votes
1answer
36 views

How to update values from another cell only once, not with subsequent edits?

I have a cell A1, whose values I change periodically. I have a cell F7:F, I add values to this column from time to time. I have another cell K7:K whose values depend on what's in A1 and what's in ...
4
votes
2answers
60 views

Using two values from one column to fill another column and repeat the calculation

I'm trying to work out how far I travel on a tank of petrol. I've set a column to display the mileage at the time I enter the petrol station, and I want to display the difference between the two ...
3
votes
2answers
44 views

How can social hashtags be extracted from a cell?

Example: A2 -> What a beautiful day! #sunday #summer B3 -> #news Stay tuned for our next contest! #something Desired output on B2 ->#sunday #summer Desired output on B3 ->#news ...
2
votes
0answers
28 views

Updating/merging rows from imported data that share the same primary key in Google Sheets

I'm using an IMPORTDATA function tied to a auto-running script to pull in a .TSV data file from an obscure webform system. That all works well, and I'm able to generate a nice dashboard from this ...
1
vote
0answers
49 views

Identifying row records in different tables when scraping through ImportHTML query in Google Spreadsheet

I'm inserting two different tables successfully with ImportHTML. However I want to identify records in rows to which table it belongs. In this particular case (link attached) the player Higuain plays ...
2
votes
1answer
20 views

Find slope/intercept of last 30 values entered?

I have a column A7:A that has numbers from 1+; I have another column B7:B, that has dates from Oct 31st, 2015 +; I have a column F7:F that has weight values; I have a goal weight of 108.36297798; ...
3
votes
1answer
84 views

How to get value of adjacent cell based on key, if the table is split into multiple columns

I need some help figuring out some Google Sheets function. I'm trying to look for the value of an adjacent cell based on a key. The problem is that the table has several rows and columns to search ...
1
vote
1answer
34 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
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 ...
1
vote
1answer
39 views

Conditionally format data in spreadsheet column, but only when it changes

Say I have this data in a Google Spreadsheet: cat cat mouse mouse mouse cat dog dog And I only want to apply conditional formatting to the ones in brackets [] (so I can hide them but still ...
4
votes
2answers
51 views

Formula based on cell positions from target cell

I have a spreadsheet that I use to keep track of my paid time off (PTO). I accrue a certain number of hours each pay period (which I know in advance). When I take some time off, I add a row and keep a ...
3
votes
1answer
65 views

Google sheets MM-DD-YYYY to DD-MM-YYYY

I downloaded CSV data from my Etsy shop to calculate taxes. I'd like to have the date "copied" properly. The CSV date I get from Etsy is formatted as "02/09/16" This is "2 September 2016" in Dutch ...
2
votes
1answer
40 views

Auto Sort Google Sheet with onEdit function

The following function successfully sorts my file (containing 3 sheets) individually. When I hit the "play" button on the script, it properly sorts (as expected) the current (active) sheet. Since my ...
3
votes
1answer
45 views

Alternate Background Colours, Conditional Formatting Based on Date?

I have a date column, A7:A, I wanted rows to be coloured every other week, A7:A may have duplicate dates but I want all the dates that lie on the same week to be one colour and alternate so it's ...
3
votes
1answer
53 views

SUM function not working for cells with formulas in Google Sheets

A B ---------------------- TOTAL =SUM(B3:B) Entry1 =IF($A3="Entry1","20",IF($A3="Entry2","170", IF($A3="Entry3","10"))) Entry2 =IF($A4="Entry1","20",IF($A4="Entry2","170", ...
3
votes
1answer
41 views

Create a symmetric matrix in Google Sheets

So, I'm working on a project in Google Sheets that has me creating a symmetric matrix. I've filled one half of it with the correct values, but I'm not happy with the idea that I have to reinput the ...
3
votes
1answer
43 views

Can I set the number format to show exactly 2 decimals only when needed in Google Spreadsheet?

I want to set the number format as below: If the number have more than 2 decimals, round it to 2 only (e.g. 12.3456 -> 12.35). If the number is whole number, just let it be (e.g. 12 -> 12).
0
votes
1answer
34 views

How to sum up values from last Monday to Now?

I have a column that totals earning, B4:B, which sums up all values, however, I just want it to total values from last (most recent) Monday to Now, until it's Monday again, start over, the date column ...