googlesheets | Google Spreadsheets R API | Data Visualization library

I am stuck and need help.

I am working with this gist https://gist.github.com/gluc/d39cea3d11f03542970b

Basically in a shiny app it provides the possibility to make CRUD maneuvers and it works perfect.

I now managed to store the data on googlesheets and also to load the data in with: read_sheet function from googlesheets4 package.

The issue is if I want to delete the last row of the table within the shiny app the first row is deleted after pressing the delete button.

This problem occurs only if I load in preexistend data (in my case from googlesheets). I have encountered the problem: The id is not updating when I click the rows in the shiny app table.

I have used browser() and went through each function but I can't find the problem.

If I click on first row and delete the first row everything works perfect, but clicking the second or any other row except the first, always the first row is deleted.

Update: I think the main issue is that after reading in the data from gogglesheets the disabled Id field is not navigating with the table, see picture.

If I click row 3 then the Id field should be 3 but it stays at 1 all the time. Therefore any action (for example deleting) on the table removes row one.

I can't get the link between the dataframe that is loaded at the beginning in the environment and the data that is defined by the CRUD application. The idea of akrun is perfect and should work but it does not:

I assign at the beginning of the code the read_sheet(...) table to responses like:

I have an array of values that i want to insert to a google sheet via API, i need to give color formatting to certain cells depending on the content of the cell like the image below:

I get to insert the values using append but i have no found any example on formatting append requests.

In googlesheets i'd like a formula that looks at an entire table and for each row that contains data it comes back as 1, and adds all those 1s togteher. All I;ve come up with some far is a formula that repeats the action of looking at one

ie

(if(COUNT(IMPORTRANGE($C11,($D11&"!"&(HLOOKUP((match(K$1,(IMPORTRANGE($C11,($D11&"!$A$3:$ZZ$3"))) ,0)),Lookups!$7:$8,2))&"6:"&(HLOOKUP((match(L$1,(IMPORTRANGE($C11,($D11&"!$A$3:$ZZ$3"))) ,0))-1,Lookups!$7:$8,2))&"6")))>0,1,0))+

(if(COUNT(IMPORTRANGE($C11,($D11&"!"&(HLOOKUP((match(K$1,(IMPORTRANGE($C11,($D11&"!$A$3:$ZZ$3"))) ,0)),Lookups!$7:$8,2))&"7:"&(HLOOKUP((match(L$1,(IMPORTRANGE($C11,($D11&"!$A$3:$ZZ$3"))) ,0))-1,Lookups!$7:$8,2))&"7")))>0,1,0))+

(if(COUNT(IMPORTRANGE($C11,($D11&"!"&(HLOOKUP((match(K$1,(IMPORTRANGE($C11,($D11&"!$A$3:$ZZ$3"))) ,0)),Lookups!$7:$8,2))&"8:"&(HLOOKUP((match(L$1,(IMPORTRANGE($C11,($D11&"!$A$3:$ZZ$3"))) ,0))-1,Lookups!$7:$8,2))&"8")))>0,1,0))+

and so on

I am trying to achieve this in Google Sheets, but nothing is working out.

Here is the table

In the column B, I want to count all non-0 cells. This is easy and I have done that.

In column C, I want to count all non-0 cells in column C, only if the cell to the left is empty. I have been able to do this too.

But now in column D, I want to count non-0 cells in column D, if and only if both the adjacent cells in column B and C are empty. I am not able to do this.

In cell D9 I should be getting a 1 - because only cell D8 has a value where B8 and C8 are empty.

How do I achieve this?

Excuse the Excel screenshot, but I want to achieve this in GoogleSheets.

Please note, I realise I can do this with if statements. However in the real dataset, I have 35 columns, so you can see where it gets cumbersome to use so many if statements.

Thank you in advance!

Trying to compare two columns in GoogleSheets with this formula in Column C:

=if(A1=B1,"","Mismatch")

Works fine, but I'm getting a lot of false positives:

Any ideas how to work this?

I am trying to pull just one cell value from a google sheet using javascript and the google sheets API, and I am using the below code, but I keep getting the below error which I can't understand because I am using the spreadsheet id and range.

throw new Error('Missing required parameters: ' + missingParams.join(', ')); Error: Missing required parameters: spreadsheetId, range

I have values from column A to column L, to collect these values I am using the following procedure:

I want, in node.js, for my command to, on the second row of my spreadsheet, set the values of A-D to 1-4. The code below seems to be how the documentation wants me to do it, but I keep getting the following error:

"GaxiosError: Invalid JSON payload received. Unknown name "majorDimension": Cannot bind query parameter. Field 'majorDimension' could not be found in request message. Invalid JSON payload received. Unknown name "values": Cannot bind query parameter. Field 'values' could not be found in request message."

I am using googlesheets, and needs help with a formula for the below usecase.

if a number from column B matches the part of the number from column A then populate specific value in column C

for example: of value from column B = 0000 matches partly in column A (its always last 4 to matches in column A) then populate APPLE in column C