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

learn more… | top users | synonyms (2)

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
31 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
10 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
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 ...
4
votes
2answers
63 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
27 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
68 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
23 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 = ...
1
vote
0answers
29 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
16 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
32 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
24 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 ...
2
votes
2answers
72 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
55 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
22 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
66 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
44 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
28 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
31 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
26 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
107 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
64 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
41 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
21 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
32 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
38 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
25 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
37 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
1answer
121 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
58 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
34 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
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
35 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
44 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
45 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
115 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
29 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
43 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
34 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
23 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
47 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
21 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
28 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
34 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
57 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
43 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 ...