My previous Sheets API post demonstrated how to add rows to Google Sheets with PHP. Â Some readers were interested in editing or deleting rows.
Google has a very informative reference here that describes the list-based feed for manipulatingÂ sheets. Â All of my scripts have been using this method, which requires the sheet to have a single header row (I’d been lucky to use that format without knowing the requirement). Â I use the example HTTP code from Google’s reference pages to give me guidance for my PHP.
I’ve added a file to the gist demonstrating how to edit a row. Â Here’s what you need to know:
When you get the sheet list feed (Section 3 of the first file in the gist), you need to add a header (“GData-Version” => “3.0”) to the GET request so that the Etag is returned for each line.
Once you have a list of entries and find the one that you want to edit, you need to grab its Etag, Id, and the edit URL (see lines 1, 2, and 9 in the image above, and click on the image to see the whole entry).
Now you just need to issue a PUT request to the edit URL and include the XML with the Etag, Id, and updated sheet data.
People who use my Insert and Delete Cell Add-on for Google Sheets have been reporting this error. Â I was unable to recreate the error until someone shared a spreadsheet with me and I found the culprit: merged cells.
What’s going on?
When you insert cells and shift the existing contents down, my Add-on selects the range of cells from your selection to the bottom of the sheet and moves it down. Â But if there are merged cells below your selection, the new range doesn’t make sense and an error is generated. Â The same thing happens if you try to delete and shift up, or if you try to shift left or right with merged cells to the right of your selection.
Why not catch the error and generate a friendly error message?
I’ve been trying, but this error isn’t triggering the try – catch section of my code. Â There also isn’t a way to detect merged cells without trying some strange tricks that would take a lot more time than I have. Â I’ve opened a bug report with Google about this.
This error seems to be Google’s version of “We’re not sure what happened, and hope that if the customer tries again it might go away”.
Google has been sending out messages saying that several of their APIs are going to be discontinued (for example, the Document List API). Â As a result, I tried to modify my custom Google Spreadsheet-populating PHP script to use the new Drive API. Â After way too much wasted time, I discovered that the Drive API can create sheets and read their metadata, but can’t add rows — for that we can keep using the Spreadsheets API.
I was successful at converting the authentication portion of my script from the old ClientLogin to OAuth with a Service Account (the script runs behind the scenes of a web site and populates a Google spreadsheet that the web site user has no knowledge of).
It wasn’t as simple as I’d hoped because the Google-provided PHP client for the Drive API doesn’t know about the Spreadsheets API. Â I had to dig around the PHP libraries to piece together authentication codes and Google_Http_Requests. Â Then Google changed the PHP client to use Guzzle, so I recently made that change.
So, if anyone else out there is interested in the combination of PHP, the Spreadsheets API, and OAuth with Service Accounts, this could save you some time.
While working with the development version of a document library that uses Oracle for storage on a Windows 8.1 laptop, we ran into the error “ODBC error: [Oracle][ODBC][Ora]ORA-28001: the password has expired”.
It turns out that up until Oracle 10, the default password policy was no password expiration. Â Starting with Oracle 11, there is a default password lifetime of 180 days.
Here are the steps we took to fix it:
1. Get DBA privileges
Assuming you’re logged into Windows as “joe”, check if you’re in the “ora_dba” group by running cmd.exe and issuing the command “net user joe”. Â You’ll see a list of local groups that you belong to near the end of the output. Â If you don’t know who you’re logged in as, try “whoami”.
If you’re not in the ora_dba group, you can add yourself with the Local Users and Groups tool in Computer Management. Â Except that tool is gone in Windows 8.1. Â Very frustrating! Â In that case, issue the command “net localgroup ora_dba joe /add”.
2. Set the password lifetime to unlimited
Now you can change the password lifetime with this series of commands:
connect / as sysdba
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
3. Change Oracle user passwords
You may or may not need to change the password of Oracle users on your system.
From the sqlplus prompt, use the following command (use double quotes around the new password and replace the username “system” as needed):
My Macbook Pro (mid-2009) hard drive crashed a few weeks ago. Â Since I had a current Time Machine backup, I figured that it would be super-simple to install a new hard drive and do a restore. Â Well, it wasn’t as simple as I’d hoped.
[update on 9/24/2014: Â This has now been published as a Google Add-on. Â Please see this page. Â To install the Add-on, just search for “insert cells” when browsing for Add-ons from Google Sheets]
I haven’t been able to find a way in Google Spreadsheets to insert a cell (or cells) in the middle of a spreadsheet and tell the existing content to shift down or to the right. Â Similarly, I’d like to be able to select cells to delete, and have them fill in by content shifting up or from the right.
So, I created a Google Apps script to add “Insert Cells” and “Delete Cells” menus to Google Spreadsheets. Â Right now, you need to copy the code from github and paste it into the Script Editor in Google Spreadsheets (see the Tools menu). Â Hopefully I’ll get the code cleaned up enough to have Google approve it as an Add-on soon!
Here’s the scenario: you have a second gmail account that gets email infrequently, but you need to monitor it. Â For whatever reason (company policy, in my case), you can’t forward the email to your main account.
I discovered that I could log into the second Google account and attach a Google Apps Script to a spreadsheet. Â The script monitors the gmail account for unread threads. Â If it finds any, it sends a message to my main account. Â The script has a time-driven trigger that can be set to run as frequently or infrequently as desired.
To use the script, make a copy of this spreadsheet.Â Open the spreadsheet and edit the cells that contain the email message parameters. Â Then go to Tools – Script editor. Â Choose Resources – Current project’s triggers. Â Add a time-driven trigger that runs the script as often as you’d like.
I love finding the forum or blog posts that have titles like “Problem I Have – Solved”. Â This is not like that.
I installed Joomla 3.2.3 on my web host where I’ve been running Joomla 1.5.26. Â The Joomla 3 installation can send mass email, but its contact forms don’t send. Â On the 1.5 install, both mass emails and contact forms send fine.
It can’t be the mail settings, since the mass emailer works fine.
It can’t be the mail program on the host, since the 1.5 install sends fine and the mass mailer on the v 3 install works.
It can’t be an issue with the domain of the web host vs domain of my From: address for the same reasons.
It could be contact form options, but I’ve tried everything I can find.
I’ve tried the tips from blog posts regarding copying libraries from a new install – no luck.
I finally had to admit defeat and installed the free version of the Flexi Contact extension. Â I hate to give up, but installing that extension got me a working contact form in a few minutes.
A friend asked me if I could repair an HDMI cable. The end was broken, and because it was run through the wall in a complicated way when the room was built, it would be very hard to run a new one.
After reading several articles online, I realized that one of the hardest parts of the job would be figuring out the wire coloring – there doesn’t seem to be a standard. The cable was labeled “LWC Digital Media Interconnect E190607-N”. I found this document that got me part of the way, but I had to use an ohmmeter and the broken end to figure out the shielded pairs. At the bottom of this post is the pinout that I confirmed.
My first try was with this solderable connecter kit. But I’m not great with super-detailed soldering, and I couldn’t wrestle the the wires into the correct places and get them soldered without shorting each other out.
There is good advice in this forum discussion, which led me to success with this hdmi field termination kit. Â This solution results in a female hdmi connection, which you then extend to the device with a short (normal) hdmi cable. With this kit, a radio shack project box, and a short hdmi cable, I was able to repair the broken cable!
Sometimes you want to find duplicate rows in a Google spreadsheet, but don’t want to examine every column — maybe you want to exclude a timestamp or other columns.
This Google Apps Script takes a list of columns that you care about. Â Then it temporarily copies those columns to a new sheet, looks for duplicates, highlights the duplicate rows in red on the main sheet, and removes the temporary sheet.
The script is attached to this Google Spreadsheet. Make a copy of this sheet, or just copy the script. Â You can do that by going to Tools – Script Editor and copying the script to the script editor of your own spreadsheet. Then edit the line that defines the important columns. After you re-open your spreadsheet, the “My Scripts” menu will show up. The first time you run the script, it will bring up the permissions dialog. You’ll need to run it again to make it work.