New answers tagged

0

At this time this it's there are only two kinds of range properties to handle its content: values and formulas. Unfortunately changing the cell content using them removes the format applied to parts of the content. In case like this, users could take a look to the Google Apps Script Issue Tracker to search for a feature request and in case that there isn't ...


1

For times that span past midnight you cannot simply subtract. Or you'll get a negative number. Start 9:00 PM End 5:38 AM Total -15:22:00 What you'll want to do (where start is column A and End is column B, etc.) is the following formula: =(B2-A2)+1 The one being equal to twenty-four hours. So: 9 pm to 5:38 am = 8:38.


-1

Why don't you simply copy this list, with each choice on a separate line (in a text file) or in cells along a column and then paste (ctrl+v) in option 1 textbox? Doing this will generate the pasted items as separate choices, simple!


2

I had installed Myanmar Zawgyi font in my Ubuntu operating System and restarted solved the issue. Now i can clearly see the Myanmar text in google spreadsheet.


2

Google Spreadsheets and the other Google Docs editors doesn't allow to upload fonts. Try choosing one of the available fonts offered by Google. For further details on how to change the font, see Change the font in your file


0

This is how such a script can work. The variable startRow is fixed at 12 here - an alternative option is to take it from the current selection, which would be startRow = sheet.getActiveRange().getRow(); The script then searches column A for "end of list" text, and then deletes the required number rows. function deleteRows() { var startRow = 12; var ...


1

No. For security reasons Google will always show the people a sheet is shared with. Your only other alternative would be to set it to public, but that would negate the option of "Specific people can access".


0

You need some marker of where the list ends, if the end is not the last row with the data. One approach, as you indicated, is to use a hidden row. For example, enter "end of list" in column A of a row, and hide that row. Then use this script, which copies the current row (where the cursor points) to the bottom of the list. function copyToBottom() { var ...


1

This requires a script, using DriveApp service to access files by name. The following function fetches the spreadsheet key of the spreadsheet with today's date (in format 2016-04-29) as the name, and places it in cell A1. (You may want to change the cell, and the timezone which is GMT in the script). Set the script to run daily, using Resources > This ...


2

A Google spreadsheet URL looks like the following https://docs.google.com/spreadsheets/d/1mPqoaJW75FlHI_nCIZ-O9MBaTQ5Y58HGf0bdzFMKd-Y/edit Just replace 1mPqoaJW75FlHI_nCIZ-O9MBaTQ5Y58HGf0bdzFMKd-Y by your own spreadsheet-key. An alternative is to use https://drive.google.com/open?id=1mPqoaJW75FlHI_nCIZ-O9MBaTQ5Y58HGf0bdzFMKd-Y The same apply, just ...


0

I did it by adding a row that contains the formula =column() Then find the column number with the vlookupfunction.


2

The message is pretty clear: you are invoking the service too often. This is discussed in Google URL Shortener 403 Rate Limit Exceeded which indicates that the acceptable rate is somewhere between 1 and 10 requests per second. Use the method Utilities.sleep to slow down the requests. For example, Utilities.sleep(1000) within the loop will ensure a 1-second ...


2

Google Docs help gives good instruction on how to make Conditional formatting, for your case Custom formula is: =$k2<today()-7


4

Without asking Google, there's really no way to know for sure. However, aside from the user agent, they could be looking for certain commonly used HTTP headers that are routinely sent by web browsers but normally omitted in curl requests. This may include, but not be limited to: cookies - saved cookies, which may include session or login credentials ...


1

onEdit can be written to take an event parameter that among other things contains the range that was edited. Changing the function declaration to take a parameter 'event' and changing r to be event.range instead of the activecell gets the exact behavior I believe you want. function onEdit(event) { var s = SpreadsheetApp.getActiveSheet(); if( ...


5

Is it at all possible to use column headers like this? Yes, it is possible. First, you need to use MATCH to get the column number of the column whose value matches 'Name'. Then you need to use ADDRESS to get the cell reference. Finally, you need to use SUBSTITUTE to remove the row number from the cell reference. =QUERY(Sheet1!A1:C,"SELECT A, B, C where ...


4

Oddly enough - in this exact situation - I played around with the page, a couple functions, variations, but the change that actually pulled in the data was simply changing the url from http to https.. see below image for live content successfully pulled


2

The function ImportJSONBasicAuthentication is basically just a wrapper function for ImportJSONAdvanced but changes some options before calling it (the line with var fetchOptions initiliaztion call sets up options accordingly). You'd need to include the function ImportJSONAdvanced and any functions it might also call (dependancies). Notice the return value ...


1

This can be done. One can get a reference to the passed range by parsing the formula in the active cell, which is the cell containing the formula. This makes the assumption that the custom function is used on its own, and not as a part of a more complex expression: e.g., =myfunction(A1:C3), not =sqrt(4+myfunction(A1:C3)). This function returns the first ...


0

Have the people inputting data use Google Forms instead. This way they will not overwrite each other. The form can submit to the sheet into a single Worksheet called 'Form Responses' by default. Create another worksheet and use the "Query" function to query and sort and clean the data as you desire. For example... Create a new worksheet in the sheet you are ...


2

The main goal is to make sum ArrayFormula. The original brilliant solution was provided here. And your formula is: =ArrayFormula( IF($Q2:Q="No", SUMIF(IF(COLUMN(J2:P2),ROW(A2:A)),ROW(A2:A),J2:P), IF($Q2:Q="Yes", SUMIF(IF(COLUMN(R2:X2),ROW(A2:A)),ROW(A2:A),R2:X), IF($Q2:Q="",""))))


1

I believe that's relying on the the JSON import library by Chris Keller, found here. As its description says, To use go to Tools --> Script Editor and add the script and save. This means the entire script, not just one function from it.


3

If these dates are entered in your spreadsheet as text, you will need to use text replacement functions. Luckily, Google Spreadsheets supports find & replace with regular expressions. So: Click Edit → Find and replace. Check the Search using regular expressions checkbox In the Find field, enter (\d+-\d+-\d+\s\d+\:\d+\:\d+)\s(\+0000) In the Replace ...


2

Here is an easily customizable function for this purpose: enter the numbers of the column to watch and the column for timestamp on appropriate lines function onEdit(e) { var colWatch = 5; // column to watch, 5 = "E" var colRecord = 6; // column for timestamp, 6 = "F" if (e.range.getColumn() == colWatch) { e.range.offset(0, colRecord - ...


1

I think the simplest path is to set an incoming webhook and trigger it with Cron. I believe you could do it all in Google Scripts if you don't mind a little lack of control with the time of the trigger (roughly 1 hr spans).


4

Use of REGEXMATCH There are many ways to do this in Google sheets. One is: =SUM(filter(B:B,REGEXMATCH(A:A,"Joe|Roy"))) to make it with sumif: =SUMIF(ARRAYFORMULA(REGEXMATCH(A:A,"Joe|Roy")),true,B:B) Filter with OR logic and this formula is ok: =sum(FILTER(B:B,(A:A="Joe")+(A:A="Roy"))) but it's not so easy to use when you want it matching more ...


1

Here are three easy steps to achieve tick/cross success: In the Google Sheets spreadsheet go to Tools -> Script Editor. Enter the following code: function onEdit() { if(SpreadsheetApp.getActiveRange().getValue() == 1) { SpreadsheetApp.getActiveRange().setValue('=CHAR(10004)'); SpreadsheetApp.getActiveRange().setBackgroundRGB(0,255, 0); } ...


1

I don't know why, but it's true: form links to new Copy of spreadsheet. Try this: Go to created Copy unlink form: Form → Unlink form Try entering new values to your form: now the result goes to the first spreadsheet.


1

I'm not sure, but it could be some sort of glich. This post may help you. I had similiar problems. I tried doing this: refreshing page changing range in the formula to force it recalculate: "Sheet1!A:C" → "Sheet1!A1:C6000" Also found smart way to make it with iferror: iferror(IMPORTRANGE("SHEET_KEY","Sheet1!A:C")) If this won't help, please give ...


1

Conditional formatting in Google Sheets does not involve setting number formats. To achieve the desired effect, you can use a script running on edit: function onEdit(e) { if (e.range.getA1Notation() == 'A1') { switch (e.value) { case 'Whole Number': e.range.offset(1,0).setNumberFormat('0'); break; case 'Percentage': ...


3

This solution uses an array formula and named ranges: Formula =ArrayFormula( IFERROR( POWER( POWER( MMULT( TRANSPOSE( N( vlookup(ROW(Categories),FILTER(ROW(Categories),LEN(Categories)),1) = TRANSPOSE( vlookup(ROW(Categories),FILTER(ROW(Categories),LEN(Categories)),1) ) ) * Values ...


2

This solution uses a helper column D (it could be hidden, or placed far to the right on the sheet). Formula in D2: =if(len(A2), C2, C2+D1), then dragged down the row. This computes a cumulative sum, restarting every time there is an entry in A column. Formula in E2: =if(len(A3), D2,), then dragged down the row. This only hides the intermediate results of ...


3

In order to close the question, I'll post my comment: its works without equal (=) sign


3

try this code: function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getRange("2:2"); var values = range.getValues(); values = values[0]; var day = 24*3600*1000; var today = parseInt((new Date().setHours(0,0,0,0))/day); Logger.log(today); var ssdate; for (var i=0; ...


3

The columns of an inline array are addressed as Col1, Col2, Col3, and so on. QUERY({"apple"; "orange"; "banana"}, "select Col1") The same applies to the arrays imported with importrange, importdata, etc.


1

Cut (instead of Copy) seems to work for me, probably for the same reason dragging selected cells works - i.e. it's the same operation. This is also the only way I know of to paste to another page without the formula changing. If you're trying to do a Copy instead of a Move, either use the drag method (copy before dragging), or copy it somewhere else on the ...


3

When you have many sumif formulas in your spreadsheet, this is usually a sign you need to use query instead. The script you are currently using to fetch the sheet names can be modified so that it creates a query formula instead: function queryFormula() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var formula = "=query({"; var sheets = ...


1

I made an adjustment to Vidar's script for anyone who only wants the autodate to work once (the first time a cell is changed). This may be helpful in a timesheet where users sometimes go back to add hours in the past and won't want the date updated to the current time. Note: For my version, I changed the range to column B, rather than D. ...


4

This is the easiest way to achieve the descending sort: =sort(importdata("http://www.tr4der.com/download/historical-prices/SPY/"),3,false)


0

Applying sort is certainly the easiest, it sometimes results in the header row getting sorted to the bottom or middle of the table. In such situations query can help, as it allows one to specify the number of header rows. The query string select * order by Col3 desc sorts by 3rd column, descending. ...


1

Using English as the main language in the Google account settings the options for the second drop-down (b) are Minutes timer Hour timer Day timer Week timer Month timer There isn't a seconds timer, so it's very likely that there is localization error. Use the Google Apps Script Issue Tracker to report it to Google.


2

This should work: =SUM(ArrayFormula(N({Failed!M2:M,Failed!O2:O,Failed!T2:U,Failed!W2:X,Failed!Z2:AC}="No, I do not have psoriasis on this area"))) After some clarification this formula seems to have worked: =COUNTIF(ArrayFormula(MMULT(N({Failed!M2:M,Failed!O2:O,Failed!T2:U,Failed!W2:X,Failed!Z2:AC}="No, I do not have psoriasis on this area"), ...


1

Try =IF( AND( ArrayFormula( FILTER( {Failed!M2:M,Failed!O2:O,Failed!T2:U,Failed!W2:X,Failed!Z2:AC}, LEN(Failed!A2:A) ) = "No, I do not have psoriasis on this area" ) ), 1, 0 )


0

As Rubén says, there's no such event built in. But you might have some (limited) success with this approach: var PROP_NAME = "lastSheetIdx"; var userProperties = PropertiesService.getUserProperties(); function didSwitchSheets(from, to) { Logger.log("Switched from " + from + " to " + to); // Your code here } function timedEventHandler() { var ...


2

If you're interested in use (more than in development), you should give a try to rankade, our ranking system. It's free to use and it's designed to manage rankings (and stats, and more) for small or large groups of players. Rankade doesn't use Elo, but its algorithm (called ree algorithm), although more complex (here's a comparison), is similar to Elo if ...


0

At this time there isn't a trigger related to switch for one sheet to another. To learn about the available triggers in Google Apps Script, please checkout Triggers and events - Google Apps Script Guides


2

This is an old post, but I was looking into this recently and figured I'd chime in. Depending on you needs, the easiest answer I could think of is by creating a drop down menu and referencing a function on another cell. Example: Select A10:A20 > right click > Data Validation Criteria = "List from range" > F2 Formula in cell F2: =mult(now(),1) To ...


1

Create crosstab report using an array formula Use the following formulas: Cell B8 : Column Headers =transpose($B$3:$B$5) Cell A9 : Row Headers ={$A$3:$A5} CELL B9 : cross tabbed values =ArrayFormula(IFERROR(vlookup({A9:A11&B8:D8},{A3:A5&B3:B5,C3:C5},2,0)))) Explanation {A9:A11&B8:D8} creates an array of keys by joining the values of the X ...


1

I could think of four approaches. Fill the sheet with single-cell vlookup formulas looking up values in that table for their row/column. Drawback: many lookups, most of which will be fruitless. The performance is likely to be poor. Try to do (1) with an arrayformula. Drawback: I don't see how to implement that purely in Sheets logic. Have a custom ...


1

The way I usually tend to do my arrayformulas, and actually any formula that requires action based on a value in another cell, is i start them off with an ISTEXT statement, and then just a simple comma in place of the else portion: ={"Start Time";arrayformula(IF(ISTEXT(B2:B),text(C2:C+vlookup(D2:D,Administrative!$A$1:$C$10,2,false),"mm-dd-yyyy ...



Top 50 recent answers are included