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

learn more… | top users | synonyms (2)

3
votes
0answers
16 views

Populate column with editable range based on drop-down

So, here's my issue. I've got three sheets. One sheet has all of the classes for Mondays and Wednesdays, one sheet has all the classes for Tuesdays and Thursdays. The third sheet has a drop-down menu ...
3
votes
0answers
124 views

IFTTT to Google Drive (Sheets) - How to write on the same row?

I'm looking for a way to get a recipe from IFTTT to Google drive (sheets) in the same row. The recipe writes in the first 3 columns, but if there is anything already in the same row it skips that row ...
2
votes
0answers
22 views

What is the behavior when Zapier matches more than one row?

Zapier has a Lookup Spreadsheet Row - action. The docs say "Lookup Spreadsheet Row - Finds a row by a column and value. Returns all the values on that matching row." What is the behavior if the ...
2
votes
0answers
22 views

Map Chart using decimal degrees in Google Sheets

I have a column with decimal degrees (locations) and another one with values. Can I plot a Map Chart on Google Sheets (with markers)? If I have to use the Script Editor, any existing (working) snippet?...
2
votes
0answers
19 views

Coordinates of an external named reference

In a spreadsheet, I've got a column named range: Sheet1!I:I, named NamedRanged1. In another sheet, I'd like to get the index of that named range, i.e., 9 (I is 9th column). I tried with ADDRESS and ...
2
votes
0answers
47 views

Google Sheets <copy Data from one spreadsheet to another>

Could you help me to understand how to made next couple: Table M Table 1 Name -> Name Qty <- Qty Table M has original field Name, Table 1 takes this name and ...
2
votes
0answers
35 views

In Google Sheets is it possible to print to a custom paper size?

I have a spreadsheet that I'd like to print on a single 8.5" x 22" sheet of paper. This is not a standard paper size, but my printer can print on it. The Google Sheets print settings dialog only ...
2
votes
0answers
76 views

Google Sheets - Dynamic Filter View by URI

I am using Google Forms to acquire "per-X" feedback, and want to dynamically produce customised reports that are filtered based on the URI. What I am looking to achieve is a unique URI per X that ...
2
votes
0answers
33 views

Import site URLs to Google Sheet (and add new row each time)

I am trying to make a list of site pages that will automatically update in Google Sheets, while keeping the full history rather than overwriting older rows with new ones. I see two possible ways of ...
2
votes
0answers
708 views

Cannot retrieve data for some exchanges from Google Finance

Since last weekend, I encounter problems retrieving data from exchanges such as SGX and SHA. =GOOGLEFINANCE(SGX:ES3,changepct) =GOOGLEFINANCE(SHA:000001,changepct) The error message is; When ...
2
votes
0answers
82 views

How to set Audio alert in Google spreadsheet that triggers by a specific value?

Can I set an Audio alert in a Google spreadsheet cell that triggers whenever it changes a specific value? I want some thing similar to "beepnow" function which is using in MS Excel VBA.
2
votes
0answers
77 views

Formatting Google Sheet Dates Inconsistent

I have a sheet where various data is entered through a form. Dates are picked on the form using the standard date picker provided by Google Forms. Some dates in a column I can change the date format ...
2
votes
0answers
94 views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries) update

Thanks for a very useful script. Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries) I am trying to do the same thing, some of my rows have only one entry and ...
2
votes
0answers
21 views

Toggle named range window with a button?

I would like to know if it would be possible to toggle the different features in sheets with a script. What I am trying to do is to basically create a button that will show the Named Ranges side ...
2
votes
0answers
124 views

I created a button in a panel (dialogue window), when I push the button I'd like to appear a new panel (dialogue) window

I created a button in Google Spreadsheet. When I push the button a custom window appears. In that window I have two buttons, one to close and one to see a new window. Everything goes well except the ...
2
votes
0answers
261 views

How to change default row height of a migrated Google Spreadsheet

The old Google Spreadsheet has a default row height of 17. The new Google Sheets has a default row height of 21. After a spreadsheet is auto-migrated by Google to the new Google Sheets, the original ...
2
votes
0answers
167 views

How do I create a map from Google Sheet data that's in another sheet?

I'm trying to create a Google My Map based on addresses in a Google Sheet "file". This seems to work OK following the standard instructions, except that I can't seem to extract data on anything except ...
2
votes
0answers
345 views

Batch Hiding Function in Google Script

I wrote a function that iterates through every non-blank row in the sheet, checks the value of a certain cell, and then if that cell matches my criteria its row number is added to an array. I then ...
2
votes
0answers
279 views

Google sheets equivalent of self-referencing in Excel

This is, in my opinion, one big advantage Excel has over Google sheets is the self-referencing formulas. What I mean by that is: say in cell A1, I'm changing the value in there a,b,c. And according ...
2
votes
0answers
116 views

Google Sheets Cells don't calculate when printed or viewing as PDF

Workbook: Very large workbook with multiple tabs of data and multiple tabs of reports using sumproduct, query and filter formulas. Payroll tab is a reflection of our master database so i'm not dead ...
2
votes
0answers
178 views

How to Modify Matched Cells Across Multiple Sheets with Google Script

My question is a fairly complex one, but I'll do my best to clarify exactly what I'd like to do and this requires some background: I have a Google Spreadsheet used for user administration in my ...
2
votes
0answers
78 views

Can't unmerge cells

I have a lot of data in a Google spreadsheet that depends on charts I spent ages trying to make look pretty. There is a stupid block of merged cells that won't unmerge, and when I try to cut-and-...
2
votes
0answers
760 views

Apps Script Webapp accessing Google Spreadsheet gets #VALUE! on every cell

I have a Google Spreadsheet with a couple of sheets that get data from other spreadsheets via ImportRange function and then calculate numeric values based on that data. Then, I assembled a code ...
2
votes
0answers
1k views

Sorting on a web-published Google Spreadsheet

I have a Google Spreadsheet that is published to the web and embedded in a website. This spreadsheet has data about hotel rooms, such as hotel name, number of rooms, date in and out, etc. I would ...
1
vote
0answers
9 views

Auto populate a field with the user who made the change

This is what I use to allow Google Sheets to auto-populate the date/time in column "N" whenever any cell in that row is updated. function onEdit() { var s = SpreadsheetApp.getActiveSheet(); var ...
1
vote
0answers
18 views

How to automatically sort a filtered list that's always changing in Google Sheets?

I have a complex spreadsheet that rolls up data from all over the place into a filtered list. We need to sort this filtered list, along with notes added afterward in columns to the right of the filter....
1
vote
0answers
23 views

Is there a script to draw a list of variables from a table to Google Forms?

I would like to have a field in Google Forms that allows the user to select a value from a "List" of values. The options in the list being drawn from a list in the sheet itself. This allows me to add ...
1
vote
0answers
30 views

Google Forms - Show unique ID on the form (before submitting)

I need an anonymous form (no names, emails etc) that shows unique ID so that if a person decided to reveal that ID to me I will be able to find his/her answers in the spreadsheet. The ID can show on ...
1
vote
0answers
15 views

Pasting multiple formulas at once

I have a column of formulas (each created by concatenating several parts of a formula in cells to the left of it on each row), I want to be able to copy and paste these into a new sheet, whilst ...
1
vote
0answers
15 views

How to use dot as splitter in currency format with Russian locale

I want to write currency in the format: ₽ 3 812.23 But if I select Russia as region in the table settings and format the string ₽ 0.00 result formatted number like ₽ 3 812,23 with a comma, ...
1
vote
0answers
59 views

How to embed Google Sheet into Google Document and keep the Google Sheet format

How do I embed a Google Sheet into a google document and keep the Google Sheet format in the document? I have got this far with the script but it's not working function updateData() { var ssUrl = '...
1
vote
0answers
42 views

How to save a Google Sheets chart as an SVG or EPS?

By default, Google Sheets only lets you save charts as PNGs. This isn't good enough when I need high-quality images, or images that are editable after export. This quick tutorial provides guidance on ...
1
vote
0answers
15 views

How can I auto-format US and international phone numbers plus extensions of various lengths in Google Sheets?

I have contact data being submitted from a bajillion sources globally. I would like Google Sheets to format the phone number field specifically to accommodate international phone numbers and phone ...
1
vote
0answers
40 views

Convert jagged array to 2D array in Google Sheets

I have two columns in a Google sheet: an ID column with a single value per cell and Color column with an arbitrary number of comma separated values per cell. I'd like to expand the Color column so ...
1
vote
0answers
20 views

Dynamic dropdowns - for sales contact management exercise

I am trying to figure out a method in Google spreadsheets where I would be able to display information of contacts after I have selected 2 more criterias before. So imagine it this way: You see a ...
1
vote
0answers
37 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 ...
1
vote
0answers
59 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'...
1
vote
0answers
52 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 ...
1
vote
0answers
58 views

How can I decrease IMPORTXML refresh interval to 10 mins?

I have a couple of IMPORTXML’s fetching the prices for products, and I want the sheet to update the prices every 10 mins. I tried now() and a functions script but to no avail. What is the best way to ...
1
vote
0answers
108 views

How do I print a Google Docs document directly from Firefox?

Using Google Docs/Spreadsheet. If I open a Google Doc and select print (either via the menu, ctrl+p, or toolbar icon) from within Chrome or Opera it then opens a print preview screen where I can ...
1
vote
0answers
32 views

How can I get locations of thousands of places saved in Google Sheets?

I have a list of ten thousand places saved in Google Sheets. I want to get their location and sort them based on that. How can I efficiently get all of their locations? Can Google Maps API be of use ...
1
vote
0answers
59 views

Filtering/consolidating data from multiple areas in Google Sheets

I am collecting data on a Google Form where users select a name then answer specific questions depending on what name they select. This leaves the resulting response spreadsheet with many blank ...
1
vote
0answers
9 views

Error message when using Trace Dependents code

I attempted to use your code to trace dependents in a google sheet found at this link: In Google Spreadsheet, finding what formulas reference a given value When I attempted to run it I received the "...
1
vote
0answers
19 views

How do I dynamically load all data from an XLS file into Google Sheets?

I have an automated service that places a new, huge (~6MB: ~20,000 rows) .XLS file into my Google Drive every day. I want to write a Google Sheets .gs script that will run once a day, and load all the ...
1
vote
0answers
29 views

How to not count blanks when using the COUNTA function

I am using the following formula to count the number of time a song name is used in a column: =COUNTA((IFERROR(FILTER('Form Responses 3'!E2:E, SEARCH(C2 , 'Form Responses 3'!E2:E ))))) It is ...
1
vote
0answers
13 views

match and lookup function on google sheets match different japanese kanji

Checkout this google sheets: https://docs.google.com/spreadsheets/d/1dw0v_64uN2lCyB7FxfoBmQ5d1OVIBEemxe5TtPaiUFY/edit?usp=sharing Despite the fact that those two kanji characters are completely ...
1
vote
0answers
8 views

Combining several workbooks into one

I have several workbooks need to merge into one. Each workbook has several tabs, it varies, some of the tabs are same name . I formats are same. In my master, I want to move these tabs. If tab name ...
1
vote
0answers
204 views

Google Spreadsheets - Snap Chart To Grid or Place A Chart In A Cell?

Is there a way to snap charts to the grid? In my spreadsheet, my chart hangs a little below my table, but I would like the chart to expand/contract with the cells on the left. If this is an easy ...
1
vote
0answers
40 views

Google Spreadsheet Filter() function randomly stops returning results

I've been having a reoccurring issue with Google Sheets. My FILTER() functions will randomly stop returning results instead saying "No Matches Are Found in FILTER Evaluation". I have to copy the ...