Rename Computer Using GSheet

[coachdnadel]

I've been happily utilizing the script from https://www.macblog.org/post/automatically-renaming-computers-from-a-google-sheet-with-jamf-pro/ (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?

[Tribruin]

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.

[ega]

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.

---

#!/bin/sh
## postinstall

# the Google Sheet identifier from the published URL 
sheetID="your_sheet_id_here"


# 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
serialCol="A"

# letter designation of the colum in the sheet for the custom device name
nameCol="C"

# 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 "https://docs.google.com/spreadsheets/d/$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
then 
dname=$serialNumber 
fi

# 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

---

[ega]

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.

[coachdnadel]

@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.

[jkaigler]

@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?

[ega]

@ 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.

[skinford]

@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:
:csv&tq=%20select%20

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!!

[angryant]

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}')

[ega]

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

/usr/bin/awk

/usr/sbin/system_profiler

[Dhuhi009]

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.

[ega]

@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.