How can zero-point energy have any measurable effect when it is just a constant offset to the Hamiltonian?

I think what is typically meant by "zero-point energy" in this context is actually zero-point kinetic energy. Exactly as you say, a constant offset $E_0$ in the Hamiltonian $H = H_0 + E_0 1!!1 $ has no consequences on the dynamics, and classically speaking, it corresponds to picking a different reference point for your potential energy function.

The kinetic energy on the other hand can have a non-zero expectation value in the ground state of the system, which is not arbitrary at all. For example, in the case of the Harmonic oscillator, this is $$frac{1}{2} m omega_0^2langle x^2 rangle =frac{hbaromega_0}{4},$$ which is not at all just a matter of picking potential energy reference. It means that the oscillator is physically moving even at zero temperature due to quantum fluctuations.

Outside of general relativity, shifting the zero point energy doesn't matter. It's changes in the amount of zero point energy that are observable.

For example, suppose you trap bosonic atoms in a harmonic potential and cool them. At a low enough temperature, most of them will condense into the ground state, a result known as Bose-Einstein condensation. But when you turn the trap off, the atoms will start to spread out, as shown at right in this famous picture:

This is because the $hbar omega/2$ zero point energy of each atom becomes ordinary kinetic energy.

As another example, consider the Casimir effect, an attractive force between two metal plates. The fact that the total zero point energy of all the modes in the electromagnetic field is infinite is completely irrelevant. The effect comes about because moving the plates changes the frequencies $omega_i$ of some of the modes, and therefore changes the total zero point energy, which is the sum $sum_i hbar omega_i/2$. Since the energy changes with the positions of the plates, you get a force between them.

The zero point energy should be the source of gravity and of space curvature. This leads to the vacuum catastrophy. Your eq. 3 makes thus problem even worse if that is possible. The zero point energy is also believed to cause the Casimir-Polder effect. https://en.m.wikipedia.org/wiki/Cosmological_constant_problem#:~:text=In%20cosmology%2C%20the%20cosmological%20constant,suggested%20by%20quantum%20field%20theory.

A constant zero-point term in total energy expression changes nothing about internal dynamics of the system. It does affect its behaviour in external field though. Adding a positive total energy $Delta E$ to a system means this system has then greater inertial mass by $Delta m = Delta E/c^2$.

So in theory one could test the prediction that there is a zero-point energy term proportional to $hbar omega$, by making careful measurements of weight of harmonic oscillators of known same composition, differing in the oscillation frequency.

Similar mass change effect is known to exist due to chemical bonds, and zero-point energy is of similar order of magnitude, so maybe it can be measured as a mass effect too.

For example, by changing isotopes one can change mass of nuclei in a lattice without changing chemical bonds too much, or only in a controlled accountable way. Then natural frequencies of oscillation in the lattice change and associated zero-point energy changes as well. Maybe the associated change in inertial mass or weight can be measured.

You are correct that a constant energy shift does not affect the dynamics of the quantum harmonic oscillator. However, relative energies do matter. It is conventional (at least in non-relativistic quantum theory) to define the energy of the free zero-momentum eigenstate as zero, and define other energies relative to that energy.

Suppose we start with a free particle (in other words, $H=P^2/(2m)$). Then the eigenstate of $P$ with eigenvalue zero also has zero energy. (Technically, this state is not normalizable and not a proper member of the Hilbert space, but we can still get proper states with arbitrarily small energy.) Now, suppose we turn on a potential so that $H$ is now the harmonic oscillator Hamiltonian. The zero point energy will have to be greater than the energy of the free particle. Now if we added a constant shift to $H$, then we could make the zero point energy equal to $0$. However, then the energy of an unmoving free particle would be negative.