Rename Computer Using GSheet

Release Candidate Programs Tester

I've been happily utilizing the script from (bless you @haircut) for naming all of my devices. Since it uses Python, am I going to have to install Python on new devices (running Catalina) and old devices that are wiped? Anyone installing Python on Catalina machines through the JPS? Do I need to worry about it Catalina?


Valued Contributor II

Python (technically Python 2.7.16) is still installed on Catalina. You should have no problems running the same script.

Be aware that Python 2 is End of Life and no longer updated or supported. Apple is warning that it will be removed in a future version of macOS. At this point, Apple has not given any indication whether they will include Python 3 at that time. It may come to a point in the future that you may have to install a Python run-time.

Contributor III

No doubt @haircut does great work.
We took his idea (full credit here) and a slightly different access to Google sheets and run this in a PreStage pkg installer as post flight. This script is setup so you can download from ASM/ABM and append to the Google Sheet with no formatting change.
I hope this helps.

## postinstall

# the Google Sheet identifier from the published URL 

# letter designation of the column in the sheet for the device serial numbers
# if following the default format from an Apple School Manager export this will be A

# letter designation of the colum in the sheet for the custom device name

# get the device serial number
serialNumber=$(ioreg -c IOPlatformExpertDevice -d 2 | awk -F" '/IOPlatformSerialNumber/{print $(NF-1)}')

# uncomment for debug
# echo $serialNumber

#Look up serial number from Google Sheet using the Google visualization api
dname=$(curl --silent "$sheetID/gviz/tq?tqx=out:csv&tq=select%20$nameCol%20WHERE%20$serialCol%3D%27$serialNumber%27")

# Remove the header information and use quotes
dname=$(echo $dname | cut -d '"' -f 2)

# uncomment for debug
# echo $dname

#Test to see if the name return has zero length and use serial number instead
if test -z $dname

# uncomment for debug
#echo $dname

# Name the device using best value
scutil --set LocalHostName "$dname"
scutil --set ComputerName "$dname"
scutil --set HostName  "$dname"
dscacheutil -flushcache

exit 0      ## Success

Contributor III

Also note that we fall back to using the serial number if the string returned from the sheet is zero length but you might want more checks.

Release Candidate Programs Tester

@ega Thank you! This script is extremely helpful! I think we will still name it from our current GSheet but that's just a simple change to the nameCol variable.

Contributor II

@ega testing your script and I am obviously doing something wrong. I input my sheet id with the serial number and the computer name. When the script I can see it gets the serial number but it returns "en" when it should be INFR999. i made no other changes to the script, the serial number is in column A and the computer name is in column C. Any ideas?

Contributor III

@ jkaigler As long as the GSheet is readable by everyone then should return the value. Could be some bad data in the cell due to cut and paste but that is all I can think of.

Contributor III

@jkaigler, hope all is good,

Wanted to start using this script and I tried it with a good friend of mine Matt and it returned the same "en", that you stated. Matt is a wiz and he found the issue, you may have already found it considering the date of this article, but for the sake of anyone else who wants to use this script, it works like a charm. Thank you to both @ega for this script and @haircut for the original script. You both are excellent in what you do, and thank you also to my friend Matt!

Here is the only fix needed, in @ega's post, in the, curl --silent line go down to the section in the line: :csv&tq=select%20 and add a %20 in front of "select".

Your fixed section would be:

And, as they say, Bob's Your Uncle! It works like a charm and naming machines couldn't be easier, unless of course, Apple did what they were supposed to do and make it simple to do tasks in labs as they used to be able to do, In What day was that??? Oh Yes, the Netboot Imaging days, but I digress. Thank you again @haircut, @ega, and Matt! You guys rock!!

New Contributor III

Thanks for this it helped me out.  I had one issue with getting the device serial numbers so I made the following change which worked for me:

serialNumber=$(system_profiler SPHardwareDataType | awk '/Serial/ {print $4}')


Contributor III

That will work too!  Note that you might want to use the full path to the binary tools for security sake.



New Contributor

Hi ega thanks for the script it works , But the computer name is coming as Version instead of the one which i want, Is there any thing needs to be done on the google sheet part lake any particular format needs to be there. some help on this will be very usefull. Thanks in advance.

Contributor III

@Dhuhi009 I ran into this issue recently.  Remove the header line in your google sheet and that will fix the issue.  Not sure what changed on the Google side but if a header row exists then the query will return 2 lines the header text first and then the value.  I was able to just clear the first row of the sheet that had my column name text in the cells.