google sheets – Auto hide row based on cell text (formula based)

My plan is to hide an entire row if a specific cell in the row showed specific value. I already found the script and the script work amazing. But the problem now, since the script use onEdit function, it only worked when I input the cell manually. Since we handle a large amount of data, this manual input is not reliable and impossible to manage.

So, do google script have a function where a script can be triggered by formula based value? I already try onOpen, onChange, myFunction but all failed. Can anyone please help me?

Here is the script that I used for my sheet.

var SHEET = "Allocation";
var VALUE = "0";
var COLUMN_NUMBER = 1

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();

  if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    if(cell.getColumn() == COLUMN_NUMBER){
      //If the cell matched the value we require,hide the row. 
      if(cellValue == VALUE){
        activeSheet.hideRow(cell);
      };
    };
  };
}

As for now, we are using the filter function. It works just fine, but its good to have this script to work.

google sheets – help with query finding multiple entries in 1 column

I am trying to query several different colors from one column. This is what I have so far:

=query('Raw Data'!C1:J1000,
"Select E, D, I, J, C, F, H 
  where H contains 'Monday' 
  and F='Green' 
  and C='schools' 
  order by E")

For example I want to query all the items that are Green, Red, and Blue in the F column. Is there a way to query for multiple selection in 1 column?

Filter by merged cells in Google Sheets?

You can try the following

=FILTER(A1:B21, 
            ArrayFormula(vlookup(ROW(1:21),{IF(LEN(A1:A21)>0,ROW(1:21),""),A1:A21},2)) >= DATE(2020, 10, 30))

enter image description here

What we do is create a virtual column for column A.

(Try the partial formula by itself and see how it behaves.)

google sheets – Changing the fontColor of cells when editing

I’m trying to write an Apps Script that change the font color of a cell when it value changes.

I found a way to to change de fontColor of a range, and add to trigger ‘onEdit’, but the all range is changing and not the single cells that I’ve been modify.

Here is my Script :

function changeFontColor() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetA1 = ss.getSheetByName('Voltaire');
  var lastRow = sheetA1.getLastRow();
  var lastColumn = sheetA1.getLastColumn();
  var dataRange = sheetA1.getRange(3, 3, lastRow -6, lastColumn);

  
  dataRange.setFontColor('Red');

} 

formulas – Google Sheets for if contains “*”

I have found the the regexmatch solution for substring: Google Sheets formula for “if contains”

However, why for “” is not working? I’m trying to make a condition if the name contains “” it will exclude the stamp duty cost.

As for now, I’m using =IF(REGEXMATCH(C11,”-“),0,ROUNDUP(G11/1000,0)). What is the explanation why “*” cannot be used?

Thank you

google sheets – Using regex to select the text between two characters IF there is text

I have this text:

<a class="_f5rco33" href="/account-settings"><div class="_ojs7nk">Account</div></a>

And I want to grab the word “Account”, but when I use this regex

">((^<)*)<"

It returns “” because “settings”><div” is the first valid instance.

My tags and text will change every time I run this, but I want to select what’s between > and <, IF it is text, and I can’t figure it out.

vlookup – Categorize text in Google Sheets based on matching partial text in the Category index

I’m looking to match, with VLOOKUP, part of the words in a column in the main sheet to an index of keywords/categories where partial words are stored for ease in another sheet.

Here’s an example worksheet:
https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

  1. The Expenses sheet has the data as we receive it, in columns A to C.
  2. In Column D, we’d like to auto-assign a category to these expenses. If we do this with exact text in the column B, for example, BANK VISA PREAUTH PYMT, then it’s easy. The VLOOKUP formula in column D works by getting this precise reference from the ‘Categories’ tab (cell A1 is worded exactly this way). This formula works: =IFERROR(VLOOKUP(B2,Categories!A:B,2,FALSE),"")
  3. The challenge is that our expenses of a similar type won’t always follow the exact text. For example: SEND E-TFR US***ke5 and SEND E-TFR US***xja. As such, we’d like to match just the text SEND E-TFR.

This means the VLOOKUP needs to match partials in the ‘Categories’ tab first column. How does adjust the VLOOKUP formula to achieve this?

PS. My question is quite similar to this one with the vital distinction of wanting to match substrings.
Categorize cells in Google Spreadsheets

google sheets – How to get the previous edit of a cell in another cell using formula or script

I’m trying to figure out a way to bring the previous edit of a particular cell in another cell. I cannot use version history because every cell may e edited at a different time in different versions. I cannot take all version create subsheet and do a vlookup, So is there any way to run script or update a formula for the same?

automation – Bulk Insert images into Google Sheets as Drawing

I have generally bunch of pics/slides which I like to add into sheets using Insert > Drawing which allows me to annotate/edit freely.

The is that I need to do that 1 by 1 which is very annoying so I am looking for a way to automate this somehow.

All advices/questions are welcome.

Edit:

As I said, I do this operation manually through sheets menu for each image I want to add:

  • Insert > Drawing
  • Insert Image
  • Close

I am looking for a way to bulk upload images as such. One drawing per image so I can annotate each separately later when needed.

object oriented – Modeling data from Excel sheets to parse to Excel sheet

Background:

I have close to 50 excel files – I cannot change the data source – and I steadily get more.
My task is to make sense of all that data and, lo an behold, save that as another excel file (I asked if I should put it in a local database, but no, I should not).

Asking on SO lead me here, as I thought it might be a better fit here, but my problem, if it even is one, is quite specific.


Code:

In my current code I used a couple of classes to model the data; I read somewhere that data should be data and not a class but are dictionaries helpful in this case?

Assembly Class:

Private pPartnumber As String
Private pDescription As String
Private pLevel As Long
Private pSupplier As Supplier
Private pCustomers As CustomerCollection
Private pSubAssemblies As AssemblyCollection
Private pWarehouse As String
Private pEligibility As String
Private pApprovedData As String
Private pApprovedDataBasedOn As String
Private BuyOrMake As AssemblyBuyMake
Public Enum AssemblyBuyMake
    Buy = 0
    Make = 1
    Both = 2
    Other = 4
End Enum
' Lots of getters and setters
' removed for conciseness

Private Sub SetEligibilityOfSubAssemblies(ByVal Value As String)
    Dim subAssembly As Assembly
    If Not pSubAssemblies Is Nothing Then
        For Each subAssembly In pSubAssemblies
            subAssembly.Eligibility = Value
        Next
    End If
End Sub
Private Sub SetApprovedDataOfSubAssemblies(ByVal Value As String)
    Dim subAssembly As Assembly
    If Not pSubAssemblies Is Nothing Then
        For Each subAssembly In pSubAssemblies
            subAssembly.ApprovedData = Value
        Next
    End If
End Sub

'@Description "Checks if Partnumbers are equal, ignores Description- and other deviations"
Public Function Equals(ByVal Assembly As Assembly) As Boolean
    Equals = (pPartnumber = Assembly.Partnumber)
End Function

As you can see my class has lots of fields; the two setter subs are private as any Subassembly automatically inherits the Topassembly’s ApprovedData and Eligibility as per requirement.

AssemblyCollection:

Private pAssemblyCollection As Collection
Private Sub Class_Initialize()
    Set pAssemblyCollection = New Collection
End Sub
Public Sub Add(ByVal Value As Assembly, Optional ByVal Key As String)
    '
End Sub
Public Sub Remove(Optional ByVal Value As Assembly, Optional ByVal Key As String)
    '
End Sub

'@DefaultMember
Public Function Item(Optional ByVal index As Long = -1, Optional ByVal Key As String) As Assembly
    '
End Function

'@MemberAttribute VB_MemberFlags, "40"
'@Enumerator
Public Property Get NewEnum() As IUnknown
    Set NewEnum = pAssemblyCollection.(_NewEnum)
End Property

The CustomerCollection looks the same.

Customer:

Private pName As String
Private pID As String
Private pCountry As String
Private pHTSCode As String
' And its getters and setters

'@Description "Returns True if Name, ID and HTSCode are the same"
Public Function Equals(ByVal Customer As Customer) As Boolean
    Equals = (pName = Customer.Name And pID = Customer.ID And pHTSCode = Customer.HTSCode)
End Function

The HTSCode is currently part of the Customer as the file I found it in contains Customer and sales data and barely enough assembly data to identify an assembly – think “customer has a partnumber for an assembly and my files have a partnumber which don’t match” and you are just supposed to know which ones refer to the same assembly.


Question:

Instead of using these classes should I use dictionaries of dictionaries?
And for comparing data of different sheets should I store either these classes or dictionaries with the data in new dictionaries or arrays?
I’m not sure if there’s a maximum on how many custom objects I can create (other than memory limits), but I fear I’ll end up with a lot of Assembly instances in this case.

Currently I

Dim arr() As Assembly

to collect all the data before parsing it to a sheet.