Menu
  • HOME
  • TAGS

How do I select a range of cells in without giving the range a name?

google-apps-script,google-spreadsheet

You can use Spreadsheet.getRange(). For example, this should work: var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("..."); var currencyRange = ss.getRange("C29:Z250"); Well, actually I'm using this Sheet.getRange() method instead, but they should be the same in your case. It offers more ways to declare the desired range, such as getRange(row, column, numRows, numColumns), which...

How to find text in a string using google script?

javascript,google-apps-script,google-spreadsheet

You need to check if the str is present: if (str) { if (str.indexOf('task') > -1) { // Present } } Alternatively, you can use test and regex: /task/.test("task is completed"); /task/.test(str); /task/: Regex to match the 'task' test: Test the string against regex and return boolean ...

Counting values embedded in strings inside a column (Google Spreadsheets)

excel,google-spreadsheet,excel-formula,formula,countif

You would have to use wildcards.. please try: =countif('Responses'!B:B,"*"&A2&"*") and see if that works ?...

Copy Row if Sheet1 A contains part of Sheet2 C

rss,google-spreadsheet,google-docs,drive,advanced-search

See if this works: =query('GitHub-Changelog'!A:F; "where A contains '"&C1&"' ") where C1 (on the same sheet as the formula) is the cell that holds the date (ex: Fri Jun 12)....

Can I add a specific sentence to a cell based off of another cells answer?

google-apps-script,google-spreadsheet

Use the debugger, to step through your code one line at a time. In order to do that, you'll need to make some changes to your code. Hard code some values, just for testing: function onEdit(e) { var editedCol = 24; //e.range.getColumn() var editedValue = 99; //e.range.getValue() var editedRow =...

How to connect PHP GAE(Google App Engine) to Google Docs, Spreadsheets

php,google-app-engine,google-apps-script,google-spreadsheet,google-docs

In PHP, you can make an HTTP Request. In Apps Script, you can create a Stand Alone Apps Script, that does nothing but take a GET or POST request, do whatever you want it to do (E.g. write data to spreadsheet), and then return something. So, you could use an...

Are there google apis to update a google sheet

google-api,google-spreadsheet

You can use the Google Sheets API. But you are probably better off using Apps Script to access Calendar and Sheets.

Google Sheets moving rows between separate spreadsheets not working but between sheets is working

javascript,google-apps-script,google-spreadsheet

I believe I was able to solve this in a simulation environment. First of all, the onEdit method apparently doesn't work when you access other SpreadSheets as it is supposed to be for simple scripts only. So you'll need to create a function with another name and run it as...

Sum multiple values in certain rows in a unique formula

excel,google-spreadsheet,spreadsheet

Adding a criteria for column E should be sufficient to cancel out the non-matching rows. In B6 use, =SUMPRODUCT(($E$6:$E$13=E6)*($F$6:$F$13)*($G$6:$G$13)) Fill down as necessary....

Google Spreadsheet - is there any way to export conditional fomatting script

google-apps-script,google-spreadsheet

There is no programmatic access to conditional formatting in either Google Apps Script or the Spreadsheet API. All you can do is get the current format (in Apps Script only). Issue 162: Ability to set conditional formatting in SpreadsheetApp That request has been active since 2010. I don't see one...

How to get Google Sheet data into a HTML page using jQuery/Javascript?

javascript,jquery,html,google-spreadsheet,google-spreadsheet-api

There is a jquery plugin called sheetrock that does this very well, I've used it for a pretty big project myself and while it can behave strangely if you have a lot of columns in a sheet it still overall performs well: https://plugins.jquery.com/sheetrock/

How do I get google sheet conditional formatting custom formulas to refer to relative cells?

google-spreadsheet,gs-conditional-formatting

$ is to "anchor" to that row/col. If you want the column to stay static, use $A2 and the row can adjust freely.

Google Apps Script: event.setTime error and time format

google-apps-script,google-spreadsheet,google-calendar

Serge has it right - the problem is that you've retrieved a CalendarEventSeries object, not a CalendarEvent. Since the only method in the service that will look for an event by ID is getEventSeriesById(iCalId), you're kinda stuck. One option is to use the Advanced Calendar Service instead: var event =...

What is this Google Spreadsheets IMPORTRANGE() Sheet key?

google-apps-script,google-spreadsheet

That's an absolute cell reference. (The sheet's key is expected to be read from cell O2.) This range syntax is supported by Excel and Google Sheets....

Code gives out “strange output”

google-apps-script,google-spreadsheet

This line: sinceLastMod=sinceLastMod+sinceLastMod+lastModH+" hours, " Should be: sinceLastMod=sinceLastMod+lastModH+" hours, " Remove one of the sinceLastMod variables. You've got it in there twice....

Filter function based on dynamic criteria from another cell

google-spreadsheet

I've found a kinky solution for this. first I've divided the problem in some smaller portions. I've created an extra column what I call the 'filter' column. Next to this filter column i've matrix with all posible filter options In this spreadsheet you can see an example of this solution...

access drive with access token

php,google-drive-sdk,google-spreadsheet,google-oauth

If you want to give the end user access to your drive, you have to give your application authority to make API calls on behalf of a user (in this case you) in your domain. For this you have to set up a service account and generate a p12 key...

Yearly quarters formula

google-spreadsheet,analytics

You could try this (add your reference cell with date or a date to the place I've pointed with **) =ROUNDUP(MONTH(*reference or date*)/3;0) ...

Using Array Formula with Split()

google-spreadsheet

Split() does not work in Arrayformula. There are workarounds. One involves using regexextract, and looks like this =ArrayFormula(IFERROR(REGEXEXTRACT(";"&B16:B,"^"&REPT(";+[^;]+",COLUMN(OFFSET(A1,,,1,3))-1)&";+([^;]+)"))) The ";" is the delimiter, the "3" would be the maximum splits/column outputs you would have in one cell. E.g: a cell with value; value; value --> would have a 3 column...

Create Spreadsheet using Google Spreadsheet API in Google drive in Java

java,google-api,google-spreadsheet,gmail-api

I finally with help from here managed to create such connection. Everything is working as before. Steps You have to do is: Register at https://console.developers.google.com Create new project Under APIs & Auth -> Credential -> Create New Client ID for Service Account When the Client ID is generated You have...

Google Spreadsheets: Format value if it is public holiday

google-spreadsheet

Thanks @Akshin Jalilov. So assume: Column A is the dates, Column B in another holiday spreedsheet is the holidays At Column C, I check if it is public holiday or Sunday: =COUNTIF(INDIRECT("holiday!$B$2:$B$14"),"="&A2)+COUNTIF(WEEKDAY($A2),"=1") If it is holiday or sunday, then the field value is >= 1 Then I add conditional formatting...

Google Docs Mail Merge script not working as of last week

javascript,google-apps-script,google-spreadsheet,google-docs

This appears to be Issue 3206, "Sending mail with attachment of Drawing (as PDF) consistently fails". Visit that defect and star it for updates. There doesn't appear to be a new scenario in your script, but if you have any additional info that might help the Googler who is responsible...

Can I move a row that is updated from another sheet?

google-apps-script,google-spreadsheet

You need to change your if statement. It should be "===" instead of "==". Here is a good explanation behind this. Here is the final script that you can use function onEdit(e) { var sourceSheet = "Raw Data"; var columnNumberToWatch = 17; // column A = 1, B = 2,...

Can I add a Trigger based on change of a particular cell?

google-apps-script,google-spreadsheet

This is code taken directly from the documentation: function onEdit(e){ // Set a comment on the edited cell to indicate when it was changed. var range = e.range; range.setNote('Last modified: ' + new Date()); } It already has e.range in it. All you need to do is add and if...

COUNTA in current row in an array formula (Google Sheets)

arrays,excel,count,google-spreadsheet,row

The formula should work if you convert the booleans (true or false) returned by LEN(A1:E)>0 to numbers (1 or 0), as Barry already mentioned. This can be done quite easily by wrapping the output of the LEN()-function in an N-function or by preceding it with '--'. So, assuming your data...

Call Google Spreadsheet script function from external app

google-apps-script,google-spreadsheet

Unfortunately, you cannot directly call your script from any external application. However, you can change it to be a web application, then invoke it via an HTTP GET or POST from almost anywhere, including an Android app. Insert new rows into Google Spreadsheet via cURL/PHP - HOW? provides an example...

Automatically export incoming emails from Outlook to existing Google spreadsheet [closed]

email,outlook,google-spreadsheet,export

Yes, it is. You can develop an Outlook add-in, see Walkthrough: Creating Your First Application-Level Add-in for Outlook to get started. The NewMailEx event is fired once for every received item that is processed by Microsoft Outlook. The item can be one of several different item types, for example, MailItem,...

Prevent users from creating new sheets in a shared google spreadsheet

google-spreadsheet,google-docs

Without seeing your existing spreadsheet, Seedmanc, Google's "Filter views" option under the "Data" menu of your Sheet could be helpful, because it lets owners/editors create and save filters that are accessible to view-only users too. What's more, view-only users can create filter views unique to them. Once they enter a...

Auto populate fields in Google Sheets

google-spreadsheet

Assuming 6/1/2015 in F1 and 6/15/2015 in G1 please try: =array_constrain(arrayformula(row(A1:A100)+F$1-1),G$1-F$1+1,1) ...

How to know or calculate empty or blank cells on google sheets?

google-spreadsheet,google-docs

Google sheets has a formula for that. =COUNTBLANK(range) ...

client error (400) using gsheet2tbl to get Google Sheet data

r,google-spreadsheet

There's a new package for reading from Google sheets... https://github.com/jennybc/googlesheets I find that it's fantastic for this sort of work. Give it a shot... devtools::install_github("jennybc/googlesheets") # run this and it will ask for user authentication... gs_ls() gs_read(ws = "Your worksheet") ...

How to put a new column after a “sort” in a google sheets doc?

google-spreadsheet

The best way for me to do this is create a custom function though google script and then use it as a formula. You can use the following script //Do Not Edit This function ADDTOTAL(range) { return range+",Total"; } And then in your sheet type with your range =SPLIT(ADDTOTAL(SORT('REPORT LOOKUP'!F:F)),",")...

How can I exclude a sheet in an array, and sort with a range

google-apps-script,google-spreadsheet

There are two questions in here: You need the index of the master sheet. Say it's the first sheet: var allSheets = ss.getSheets(); //get all the sheet var indexOfMaster = 0; //or whichever the index is var sheetsExceptMaster = allSheets.splice(indexOfMaster,1); //splice out 1 item at this index, which would be...

Google Spreadsheet custom function to add csv in spreadsheet

google-apps-script,google-spreadsheet,google-spreadsheet-api

I guess this way cant work, based on this article, others are facing the same issue. I went with the image workaround that assigns the script to an image and gets called when you press the image. The following error message is displayed: You do not have permission to call...

Edit a Google Spreadsheet using Google Apps Script

google-apps-script,google-spreadsheet

.getRange and .setValue are the methods you will want to use in whatever you are trying to do. This little function will just take the cell notation as the first argument, and then the value you want to set as the second. function setCellValue(cellName, value) { var ss = SpreadsheetApp.getActiveSpreadsheet();...

Trying to use regexmatch with string negation

regex,google-spreadsheet

You might want to use a built-in NOT() operator (see Filter Sample Usage): =sum(filter(fUsers,not(regexmatch(fLabel,"2nd")))) EDIT: Accoding to RE2 regex syntax, negative look-aheads are not supported. (?!re) before text not matching «re» NOT SUPPORTED This means, you cannot check with a regex if a sequence of characters is missing in the...

Add 5 Business Days to a timestamp

google-spreadsheet

As far as I understand adding five business days to current date is same as adding a week. Try this: var time = new Date(+new Date + (1000 * 60 * 60 * 24 * 7)); time = Utilities.formatDate(time, "GMT-08:00", "MM/dd, E"); ...

Can I associate a user's account with an action to the drive API?

javascript,oauth,google-drive-sdk,google-spreadsheet,google-oauth

The short answer is no. You'll be using the spreadsheets API (NOT the Drive API) to update the sheet.As far as Google is concerned, the "user" is your application, regardless of which human was driving the application at the time. Your application knows who the human is, and so it...

Using Google App Scrips to produce Input Messages

google-apps-script,google-spreadsheet

Here is how you do it. Add as many "else if" functions as you want. I formatted it in the it would be easy for you to edit function onEdit(e) { var cell = e.range; var cellColumn = cell.getColumn(); var cellSheet = cell.getSheet().getName(); var cellValue = e.value; var sheet =...

Change/replace part of an URL contained WITHIN a function

javascript,replace,google-spreadsheet

Instead of including DATA1 in the URL, append it with data coming from f.ex a textbox: function loadChart() { var txtbox = document.getElementById('textbox').value; var url = 'https://docs.google.com/spreadsheet/ccc?key=...&sheet=' + txtbox; var query = new google.visualization.Query(url); query.send(handleQueryResponse); } ...

Convert from base 10 to base 3

google-spreadsheet,base

You could use a formula written in Apps Script: function toTernary(decNumb) { var str = ''; do { str = String(decNumb % 3) + str; decNumb = decNumb / 3 | 0; } while (decNumb >= 1); return parseInt(str); } Results: Usage: Tools > Script Editor > Blank Paste the...

On Change Trigger Not Running Script On Change

google-apps-script,google-spreadsheet

Run it on a time trigger that runs every minute until Google addresses the issues of not catching the on change event and/or not being able to define open-ended named ranges. Edited for running the script on open To keep the sheet from recalculating everytime it is opened whether needed...

Google Sheets Script - changing the contents of a cell based on a given condition

google-apps-script,google-spreadsheet

I took a look at your spreadsheet and added some code: function forButton() { //Logger.log('it ran!'); var ss = SpreadsheetApp.getActiveSpreadsheet(); var activeSheet = ss.getActiveSheet(); var masterSheet = ss.getSheetByName('Master'); var inputValues = activeSheet.getRange(2, 1, 1, 3).getValues(); var secondInput = inputValues[0][1]; var allColumnTwoVals = masterSheet.getRange(2, 2, 6, 1).getValues(); allColumnTwoVals = allColumnTwoVals.join().split(","); Logger.log('secondInput:...

SPLIT using “ ” delimiter in Google Sheets won't always preserve period following number

regex,string,split,google-spreadsheet,string-split

This may get a little 'ugly' but see if this works: =ArrayFormula(iferror(REGEXEXTRACT(" "&A1:A,"^"&REPT("\s+[^\s]+",COLUMN(OFFSET(A1,,,1,6))-1)&"\s+([^\s]+)"))) 6 in the offset denotes the (maximum) number of columns/groups that are outputted. as this is an arrayformula, no 'dragging down' is needed. ...

Formula Cell value is not updating in the reference while Using Google Spreadsheet API with JAVA

java,excel,google-spreadsheet,google-spreadsheet-api

getCell() only reads the local information about the cell, i.e. the stuff you inserted. You'll have to get the cell again from a CellFeed, then go through the entries to find you cell again to read back the online information. service.getFeed(cellFeedUrl, CellFeed.class); for(CellEntry ce : cellFeed.getEntries()) { Cell cell =...

SpreadsheetApp.getActiveSpreadsheet() is breaking script

google-apps-script,google-spreadsheet

You must define the method to get data in a .gs file and call it with google.script.run. In some Code.gs file: function getSheetData() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var data = sheet.getDataRange().getValues(); return data; } And in the html script: function onScrapeClick(){ // Disable Button this.disabled = true; // Get...

Ignore #VALUE! error in SORT function

sorting,google-spreadsheet

Your existing google-sheets formula can make use of the iferror function. =sort(unique(transpose(split(join(";",iferror(A:A, ""))&join(";",iferror(B:B, "")),";"))),1,TRUE)       ...

Google Spreadsheet (Using named range in script)

google-apps-script,google-spreadsheet

You are making a simple spelling error, setBackground in this case is without s because you are setting a single color to multiple cells (the argument is "#FFF") So the code is : function resetCheckDirectory() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var tableRange = ss.getRangeByName("NamedRange1"); tableRange.setBackground("#FFF"); }; to summarize : setBackground...

Extract last word from strings

split,google-spreadsheet,string-split

There are many way to do so. One is to split on spaces and discard what is not required. Another is with a formula such as conventional in Excel (count the spaces by comparing the length of the string in full and after substituting spaces with nothing) and feed that...

Increment count in column based on value in column

excel,google-spreadsheet,excel-formula,array-formulas

In a google spreadsheet you may want to try: =ArrayFormula(iferror(SORT(ROW(A1:A),SORT(ROW(A1:A),A1:A,1),1)-MATCH(A1:A,SORT(A1:A),0)-ROW()+2)) Example sheet...

Google Sheets Adds-on open automatically when user opens a sheet or clicks a link in worksheet

google-apps-script,google-spreadsheet

Sure, you can get a UI component for an add-on to open automatically when a document is opened. This is HORRIBLE behavior for an add-on, and it should never be published because it would interfere with other add-ons. But still, it can be done. There are some restrictions, though. The...

Google Spreadsheet- Based on text in different cells, change another cell text

excel,google-spreadsheet,spreadsheet

For the first function, you would need to enter the following in cell F3: =IF(AND(C3="passed",D3="passed",E3="passed"),"passed","failed") This checks whether C3, D3 and E3 contain "passed" and if so returns "passed". For the second function, this is very similar: =IF(OR(C3="passed",D3="passed",E3="passed"),"passed","failed") This checks whether any of the three cells contain "passed", and if...

Google sheet app script errors after laptop sleep

google-apps-script,google-spreadsheet

Henrique provides a good explanation of why custom functions like this don't update in Google Apps - script to summarise data not updating. It comes down to an optimization decision in Google Sheets; a custom function will only be re-evaluated if its parameters have changed. Try to set up an...

How do I make a Sidebar display values from cells?

google-apps-script,google-spreadsheet

Something like this? This add-on uses the poller idea from How to poll a Google Doc from an add-on to call a server function getRecord(). That function grabs the row of data that is currently selected, and returns it to the showRecord() callback on the client (JavaScript) side, which handles...

How do I stop google sheets reassigning formulae values on another sheet

google-spreadsheet

Here is a workaround. Use INDIRECT() with ROW() =INDIRECT("'formsitesheet'!A"&ROW(A1)) Or =INDIRECT("'formsitesheet'!A"&ROW(A1)+X) Where X is your offset. Hope this helps...

Any way te read an excel file from a Google Sheet?

excel,google-apps-script,google-drive-sdk,google-spreadsheet,google-apps

im giving you an algorithmic-level answer without code because you also have none. with apps script you can import the xls to drive as a new spreadsheet at regular intervals. then, copy its contents to a fixed spreadsheet (replacing previous content). other spreadsheets can read from that fixed sprradaheet with...

Selecting all rows with distinct column values - Google query language

google-spreadsheet,gdata

Assuming you have the example table in range A1:C, I think the expected result could be achieved with this formula: =ArrayFormula(iferror(vlookup(unique(B1:B),{B1:B,A1:C}, {2,3,4}, 0 ))) See if that helps ?...

Extract matching data from varying number of row

google-spreadsheet,spreadsheet,pivot-table

I think this is possible using a formula: ={QUERY(A2:D,"select A,count(D) where A <> '' group by A pivot C"),QUERY(A2:D,"select count(D) where A <> ''group by A pivot B")} Here is an example sheet (the formula is in cell F1): https://goo.gl/T9xI1v...

Google Sheets API for clickable images in Google Sheets

google-apps-script,google-spreadsheet,google-spreadsheet-api,google-spreadsheet-addons

Can we also insert this clickable image and link it to app script from our server using Google Spreadsheet API? No. You can't even change the color of a cell. The best you can do is change a formula in a cell. Or Can we call app script from...

Date converted to plain text in google spreadsheet

google-spreadsheet

Your legacy file may be formatting it as a number, in which case you need to change your G31 to a number as well. Try =Int(Text(Today(),"yyyymmdd"))

Google Spreadsheet setBackgroundColors() “Deprecated”

google-apps-script,google-spreadsheet

Use setBackgrounds(). With an s since it's a method that applies multiple background colors to multiple cells

Function to check for first blank cell

javascript,loops,google-apps-script,google-spreadsheet

I am not sure of what is the format of your startRow and startCol variables, nor to understand what you are trying to do but I think you should update the variable range in the while loop, because isBlank tests if the whole range is blank or not : https://developers.google.com/apps-script/reference/spreadsheet/range#isBlank%28%29

Using the UrlShortener API in a custom Spreadsheet function

google-apps-script,google-spreadsheet,google-docs,add-on,url-shortener

EDIT: The OP pointed out in the comments that this is a custom function. Custom function run with limited authorization. A complete list of what is available is at: https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services Below uses the REST API to get the shortened url. This will work with custom functions. You will just need...

ISO-8601 String to Date in Google Sheets cell

google-spreadsheet,google-docs

Try this =CONCATENATE(TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),1),"yyyy-mm-dd")," ",TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),2),"hh:mm:ss")) Where A1 can be a cell with ISO-8601 formatted string or the string itself....

Highlight entry if not 17 numeric digits

google-spreadsheet,gs-conditional-formatting

Go to Format tab in the google spreedsheet, then go to Conditional Formatting, then in the Apply to range box, put down your range, say A1:A30 the range where you want to apply your formatting. Then, in the Format cells if, choose Custom formula is, the last one in the...

How to SUMIF on a dynamic range that includes blanks?

google-spreadsheet,formulas

I may be missing out on something, but can't this be accomplished by simply using open-ended ranges ? =sumif(B2:B, "CC/*", A2:A) ...

Access spreadsheet via Google API invalid_grant

java,google-api,google-spreadsheet,gmail-api

Try adding access_type with the value offline to your auth request. I had a very similar error to this not 2 days ago and this fixed the problem. Also, just to confirm the clientID value should be similar to @developer.gserviceaccount.com They basically expect the email_address value from the console api...

Parsing specific text from cell

regex,google-spreadsheet,spreadsheet

Maybe try =regexextract(D2, ":\s(.*?)<") ...

Need formula for next letter in alphabet in google spreadsheet

google-spreadsheet

Use this instead: =CHAR(CODE(A1)+1) ...

Macro or other solution in excel to automate interaction of data in two worksheets to come up with a third worksheet

excel-vba,google-spreadsheet

I found a solution by Spioter at Excel vba to create every possible combination of a Range which proved immensely helpful! Modified the script to fit my needs and sheet which does not reflect the Google Sheet link in the question. Thank you Spioter! Sub sub_CrossJoin() Worksheets("Combinations").Range("A4:B2000").ClearContents Dim rg_Legend As...

Can I use a modifier in Gsheets regexmatch()

regex,google-spreadsheet

In GDocs, you need to use a CONCATENATE function to append a subpattern to another pattern. Just using + won't do. Instead of just B16, use CONCATENATE("(?i)",B16) And the whole formula would look like =sum(filter(gaUsers,regexmatch(gaCategory,"new user funnel"),regexmatch(gaAction,CONCATENATE("(?i)",B16)))) ...

Email notification in google apps script

google-apps-script,google-spreadsheet

This looks a lot like what you're trying to do. For checking whether the value has truly changed after the edit, this gives you all the necessary information about it. Hence, you would know whether the new value is the same one or not. Implementing the "workaround" talked about in...

Counting unique values in Google Spreadsheet based on multiple columns

google-spreadsheet,unique

To produce the StoreName and uniqueCheckins output columns: =QUERY(QUERY(B:E,"select B, E, count(C) group by B, E",1),"select Col1, count(Col2) group by Col1 label count(Col2) 'uniqueCheckins'",1) However this will omit any StoreName that doesn't appear in the raw data (in your example, w4). Would this be OK?...

Javascript (Google Scripts) value from a function is not returning

javascript,google-apps-script,google-spreadsheet,google-calendar

Even if I'm not A JavaScript expert, I'd find it more logical to write it like that (assigning a value directly to the EventId variable) : ... var EventId = createEvent(calendarId,title,startDt,endDt,desc); Logger.log('id after func = '+EventId); sheet.getRange(lr,EventIdHolder,1,1).setValue(EventId); } function createEvent(calendarId,title,startDt,endDt,desc) { var cal = CalendarApp.getCalendarById(calendarId); var start = new Date(startDt);...

How to implement rangeProtect() over multiple tabs - google spreadsheet

function,google-apps-script,google-spreadsheet,spreadsheet

Answering my question, The main script discribs the way to find the sheet and the range(s) to protect / unprotect, for different users using the remove/addEditors(mail) function //main script - where to protect - who can edit function Protect_UnProtect( myrange, mysheet, mydescription, startRow, colnumber, numRows, columncount) { var ss =...

Output Google Spreadsheet to XML/RSS/Atom using Google AppScript Content Service

xml,google-apps-script,rss,google-spreadsheet,google-docs

getRange() function only assigns a range. You need to use getValues(). try changing title to this. var title = ss.getSheets()[0].getRange("A1:A").getValues();...

How to upload a data frame into a Google Spreadsheet

r,google-spreadsheet

First register the Speadsheet: Test<- register_ss("Year") Second access Workingsheet: test<- edit_cells(Year, ws = "January", input = Test, header = TRUE, trim = FALSE) ...

How to get the last entry containing a certain value in Google Spreadsheet

google-apps-script,google-spreadsheet,google-form,formulas

Assuming your data starts in row 2, try: =ArrayFormula(iferror(vlookup(unique(B2:B), sort({B2:B, A2:A}, 2, 0), {1, 2}, 0 ))) Change ranges to suit. Don't forget to include the sheetname if you need that formula to appear on another sheet....

How to show multiple Date/Times per location?

google-spreadsheet,spreadsheet,google-spreadsheet-api

I’m not quite sure what answer you are expecting but wanting to post an image (and probably length!) is why this is not a comment. Poor data layout that requires changes to help legibility or changes to facilitate further processing is, IMO, a very big issue – much more so...

Calc/Spreadsheet: combine/assign/migrate cols

excel,google-spreadsheet,openoffice-calc

Thank you @PsysicalChemist, the VLookup function is working in Calc to.

Google Sheets - How to create add and subtract buttons to track inventory

button,google-spreadsheet,spreadsheet

You can use Google Apps Script for this. If you are unfamiliar, in your particular spreadsheet, go to Tools → Script Editor and then select the Blank Project option. Then you can write functions like this to achieve what you want! function subtractSold() { var sheet = SpreadsheetApp.getActiveSheet(); var c1...

If Statement Not Working on Google Spreadsheets

google-spreadsheet

try using this = IF (EXACT(E3,E4), THEN Whatever)...

Google Form to Document Merge to Pdf to Mail

google-apps-script,google-spreadsheet

when replacing multiple keys in a document you are quite right that the keys must be unique. Personally I bracket all keys with # symbols which works for me e.g. #key1# won't be confused with #key10#

fetching specific columns using listFeed in googlesheet using java api

java,google-spreadsheet,google-spreadsheet-api

You can select columns if you use the Visualization Query Language Example that selects columns B and C (2nd and 3rd) of a big sheet (from this tutorial): https://docs.google.com/a/temet.ch/spreadsheets/d/1jwKyiVro8ZqPr5jRsyCN7igPJ76vOvtRy_xq3I8nKVI/gviz/tq?tqx=out:html&gid=0&tq=select%20B,C The query returns JSONP if you leave away the tqx=out:html parameter, but you won't get ListEntries. Note that the column names...

Conditionally formatting multiple rows with the same conditions

google-spreadsheet

That is actually pretty simple. You right click one of the cells to do the formatting(one with regular for example) and select Conditional Formatting on the menu. Then you make sure the Range is the cell you right clicked, select "The text is exactly" in the list and type "Regular...

How do i stop google sheets skipping a row with my formula when a new answer is entered?

google-spreadsheet,formulas

Just answered a similar question here. The workaround is to use INDIRECT() with ROW() =IF(INDIRECT("'Form Responses 1'!B"&Row(A16)) = "dog","1",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Cat","2",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Frog","3",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Bird","4")))) Or =IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X) = "dog","1",IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X)="Cat","2",IF(INDIRECT("'Form Responses...

how i create a formula of payment using weeknum formula

google-spreadsheet,week-number

I made a extra sheet in the spreadsheet you shared, called 'JP'. In cell A1 I entered this formula: =ArrayFormula(query({weeknum(Foglio2!A2:A)\Foglio2!B2:D}; "select Col2, Col1, sum(Col3) where Col2 <>'' group by Col2, Col1 label Col2 'Name', Col1 'Weeknumber', sum(Col3) 'Total'")) This formula outputs three columns: one column with the names, a second...

Google Sheets Copy Down Issue

google-spreadsheet,spreadsheet,google-spreadsheet-api

As JPV commented, the "pushing down of formulae" by form submissions may be counteracted by using an array formula (in row 2): =ArrayFormula('Form Responses 1'!N2:N) And as Akshin commented, if you were in fact bringing over a couple of columns for graphing purposes, you can do it all in one...

Google Sheets - propagate column date formatting to new rows

google-apps-script,formatting,google-spreadsheet,rows,propagation

Create a function that will run when the form is submitted: Managing Triggers Manually - Google Documenation Code.gs function respondToFormSubmit() { //Format entire columns var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var range = sheet.getRange("M:Y"); range.setNumberFormat('dd.mm'); }; ...

Merge Multiple Google Spreadsheets into One Sheet

google-spreadsheet

Use embedded arrays. The simplest form is ={Sheet1!A!:B5;Sheet2!A1:B5}...

Nslookup or dig in Google App Script

google-apps-script,google-spreadsheet,nslookup,dig

Find a web service that will let you use a GET or POST anonymously to query DNS info using a RESTful API, and you'll be able to use UrlFetchApp.fetch() to access it. For example, StatDNS has a simple API. Here's a custom function that will resolve a Domain Name to...

How to pass the “e” parameters to a function called with a trigger?

google-apps-script,google-spreadsheet

It was a bug. I have noticed that is better to delete the trigger, save the script, and re-create it when changes are made to the function that it calls. This way I never had problems, not doing caused strange behaviors.

How can I get keyboard events in Google Spreadsheets?

google-apps-script,google-spreadsheet,spreadsheet

keyboard events for cells cannot be handled from apps script. you can only detect a cell change once it has beeb commited (user typed and pressed enter for example), which is not a keyboard event (its a range edit event)

Most Common Value within Sublists

excel,google-spreadsheet

Are you familiar using PivotTables? You could just create a PivotTable with ID as a Row Label and Behavior as a column label and Value. Then it would just be a matter of copying/pasting those values and using a MAX formula to get the greatest behavior count.

Google spreadsheet script authorisation to BigQuery

google-apps-script,google-spreadsheet,google-bigquery

Unfortunately you can only allow access to scripts running as 'you' if it is running as a web app. The only way to run it as a webapp is if the doGet()/doPost() function is called by the browser. Running the doGet() as a function runs it as a normal script....

Google Apps Script: Bi-directional sync when rows are deleted

google-apps-script,google-spreadsheet

You just have to delete the contents of the sheet before you set the new values. toWorksheet.clear() toRange.setValues(thisData.getValues()); ...

gdata spreadsheet library for python not working anymore?

python,google-spreadsheet,gdata

Actually here is the answer to the problem: The use of client login (using username/password instead of oauth2) is likely the cause of the error. That protocol was deprecated 3+ years ago and was just shutdown. If you capture the HTTP response (which appears to have some HTML content), that...

Google Sheets Addon or Formula that accomplishes the same thing as the Prevsheet Macro's for Excel

vba,google-spreadsheet

You can do that only using a script. Try this: function PrevSheet(range) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); var active = ss.getActiveSheet().getName(); for (i in sheets) { if (sheets[i].getName() === active) { return (sheets[i-1].getRange(range).getValues()); } } } The only problem is, you will have to push the...