Extract matching data from varying number of row

Tag: google-spreadsheet,spreadsheet,pivot-table

I am trying to build graphic indicators from a (potentially) large set of data using google-spreadsheet.

So far, I've used a pivot table to extract the information from the raw data, and I want to build intermediate tables to calculate the different values I need for my indicators.

So far, my pivot table looks as follow :

                     Status1 | Status2 | Status3 | Status4
                     -------------------------------------
Country1 | Domain1 |    1    |    2    |   1     |   
           Domain2 |         |    2    |         |   
----------------------------------------------------------
Country2 | Domain1 |         |         |         |   1
----------------------------------------------------------
Country3 | Domain2 |         |    1    |         |   3
           Domain3 |         |         |   1     |

And I would like to generate the following table (there is a fixed number of Status & Domain) :

         Status1 | Status2 | Status3 | Status4 | Domain1 | Domain2 | Domain3
         -------------------------------------------------------------------
Country1 |    1  |    4    |   1     |    0    |    4    |   2     |   0
----------------------------------------------------------------------------
Country2 |    0  |    0    |   0     |    1    |    1    |   0     |   0
----------------------------------------------------------------------------
Country3 |    0  |    1    |   1     |    3    |    0    |   4     |   1

As the number of country and the number of rows present in the pivot table for each country will vary, I am unsure as to how to generate this table.

So far I was able to generate the 1st column using the following formula : UNIQUE(FILTER('myRange',NOT(ISBLANK('myRange')))), but I don't know how to proceed afterward. Is there a way to dynamically extract such data ? Should I change my pivot table ? Maybe build a pivot table using my 1st pivot table ?

Best How To :

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 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...

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 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...

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

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...

Calc/Spreadsheet: combine/assign/migrate cols

excel,google-spreadsheet,openoffice-calc

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

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 ?...

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...

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...

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...

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...

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...

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. ...

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

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()); ...

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,...

Sheet to Sheet Data Transfer with one constant in each sheet

excel,spreadsheet

On sheet 2 use this formula in cell D2 to get the sales. =INDEX(Sheet1!C:C,MATCH(Sheet2!A2,Sheet1!B:B,0)) In E2 use this to get the rank =INDEX(Sheet1!A:A,MATCH(Sheet2!A2,Sheet1!B:B,0)) Copy down. ...

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 =...

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 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...

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...

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...

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...

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 =...

If Statement Not Working on Google Spreadsheets

google-spreadsheet

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

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...

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...

Auto remove, if data already exist on other column

excel,spreadsheet,autofill

You just need to add an AND condition that excludes the values Passed in Sheet1's column F. =IFERROR(INDEX(Sheet1!A$2:A$9999, SMALL(INDEX(ROW($1:$9998)+((Sheet1!E$2:E$9999<>"Passed")+(Sheet1!F$2:F$9999="Passed"))*1E+99, , ), COUNTA(A$1:A1))), "") This method is based on excluding non-matching results so if we want to keep the entries that have Passed in column E, we use +(Sheet1!E$2:E$9999<>"Passed") to exclude...

Spreadsheet if else sum

excel,if-statement,spreadsheet,calc

something like: if(exact(e4;"bought");i4-f4;if(exact(e4;"sold");f4-i4)) works for you???...

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); } ...

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#

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.

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...

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) ...

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...

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 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) ...

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...

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();...

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 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...

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...

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) ...

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...

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)....

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 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....

Excel - Pulling data from one cell within a list

excel,powerpoint,spreadsheet

If you have two columns of the shirt numbers and the corresponding player names then vlookup() will do this, but a warning : are shirt numbers unique i.e. one player one number... With a list of numbers in D2 to D8 and corresponding names in E2 to E8, then =VLOOKUP(A2,D2:E8,2,0)...