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

learn more… | top users | synonyms (2)

1
vote
0answers
32 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
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
33 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
29 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
83 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
59 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
27 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
90 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
48 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
31 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
38 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: ##,### ...
3
votes
2answers
128 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
67 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
48 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
51 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
28 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
195 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
64 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
33 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
40 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
52 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
54 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
151 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
31 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
40 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
46 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
21 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
86 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
37 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
72 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 ...