How can you get AppSheet to pull in the picture displayed of a house in Google maps?

[Dunrite]

I’m trying to figure out how to get my app to display a thumbnail image of the photo that shows in Google maps for a particular address.

We are creating something for our repair guys to use to report their findings during an inspection at a home. Often times they aren’t even sure if they are at the correct house. So when the app pulls in the address and they see their list of assignments for the day, I really want it to display the photo of the home, that shows up on Google when you put in the address. Currently it displays a thumbnail of the map, which isn’t necessary because it’s to small to see the map as a thumbnail anyway. And when they tap the address, it pulls up directions to the property. So what I really would prefer is that when they arrive at the property, they can look in the app at the photo displayed (the same one that pulls up on Google maps) and they can confirm they are at the correct location. I hope this makes sense. And I hope someone can tell me how to make this happen. So far all I could find was looking up the house address manually and adding a URL to the image, in my spreadsheet at the time of entry. That would be a lot of manual work though so I’m certain there is an easier, more automated way to do this, right?

[GreenFlux]

This sounded like a fun challenge to try in Apps Script so I gave it a shot this morning.

I came up with something that kind of works, but it’s not ready for production yet. Posting in case anyone else wants to experiment with the code.

const sheetName = 'Address';
const addressCol = 1; 
const imageCol = 3;

function getAddressImage(e) {
  if (!e){return 'not ran from trigger'}
  const ss = e.source;
  const sh = ss.getSheetByName(sheetName);
  const triggerRow = e.range.getRowIndex();
  const triggerCol = e.range.getColumn();
  const address = sh.getRange(triggerRow,addressCol).getValue();
  
  if (triggerCol == addressCol && address !== '' && triggerRow !== 1) {
    
    // // GET MAPS SITE DATA
    const addressEncoded = address.toString().trim().replace(/ /g,'+').replace(/,/g,'+').replace(/\+\+/g,'+').replace('#','');
    const mapUrl = 'https://www.google.com/maps/place/' + addressEncoded;
    const siteData = UrlFetchApp.fetch(mapUrl).getContentText();
    
    // FIND PANOID & BUILD URL
    const pStart = siteData.indexOf('panoid=');
    const pLen = 22;
    const panoid = siteData.slice(pStart + 7,pStart + pLen + 7);
    const imageUrl = 'https://streetviewpixels-pa.googleapis.com/v1/thumbnail?output=thumbnail&cb_client=maps_sv.tactile.gps&panoid=' +
      panoid + '&w=647&h=319&thumb=2&yaw=221.26518&pitch=0';
    sh.getRange(triggerRow, imageCol).setValue(imageUrl);
    
    Logger.log(imageUrl)   
  }
  
}

INSTRUCTIONS

1. Open GSheets Menu> Tools> Script Editor
2. Copy/paste code into script, name & save script, and then click run.
3. Approve script permissions
4. Create a new script trigger for onEdit to run the getAddressImage() function.

[Dunrite]

You are so awesome for doing this! I have NO IDEA what I am doing yet. I’m still trying to figure out how this all works…but I’m getting there. Thank you so much for doing all of that! You have no idea how much I appreciate you.