GetCoords.gs

function getCoordinatesForEmptyRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Final"); 
  const data = sheet.getDataRange().getValues();
  const apiKey = PropertiesService.getScriptProperties().getProperty('GOOGLEMAPSAPIKEY');

  for (let i = 1; i < data.length; i++) { 
    const name = data[i][0];
    const url = data[i][2];
    const lat = data[i][3];
    const lng = data[i][4];

    if (!lat && !lng) { 
      try {
        let result = parseUrlForCoordinates(url, apiKey); 
        if (!result && name) {
          result = fetchCoordinatesByName(name, apiKey); 
        }

        if (result) {
          sheet.getRange(i + 1, 4).setValue(result.lat); 
          sheet.getRange(i + 1, 5).setValue(result.lng); 
          Logger.log(`Coords found for line ${i + 1}: ${result.lat}, ${result.lng}`);
        } else {
          Logger.log(`No coords for line ${i + 1}`);
        }
      } catch (e) {
        Logger.log(`Error for line ${i + 1}: ${e.message}`);
        continue;
      }
    }
  }
}

function parseUrlForCoordinates(url, apiKey) {
  if (url.includes('/search/')) {
    const regex = /search\/([-+]?\d*\.\d+),([-+]?\d*\.\d+)/;
    const match = url.match(regex);
    if (match) {
      return { lat: parseFloat(match[1]), lng: parseFloat(match[2]) };
    }
  } else if (url.includes('/place/') && url.includes('data=')) {
    const dataString = url.split('data=')[1];
    const decodedCoordinates = decodeDataParameter(dataString);
    if (decodedCoordinates) {
      return decodedCoordinates;
    }
  } else if (url.includes('/place/') && url.includes('1s')) {
    const regex = /1s([^:]+:[^&]+)/;
    const match = url.match(regex);
    if (match) {
      const placeId = match[1];
      return fetchCoordinatesFromGoogle(placeId, apiKey);
    }
  }
  return null;
}

function decodeDataParameter(dataString) {
  try {
    const segments = dataString.split('!');
    for (let i = 1; i < segments.length; i++) {
      const segment = segments[i];
      if (segment[1] === 'x') {
        const lat = parseInt(segments[i + 1].slice(2), 10) / 1e7;
        const lng = parseInt(segments[i + 2].slice(2), 10) / 1e7;
        return { lat, lng };
      }
    }
  } catch (e) {
    Logger.log(`Error while decoding data= : ${e.message}`);
  }
  return null;
}

function fetchCoordinatesFromGoogle(placeId, apiKey) {
  const baseUrl = 'https://maps.googleapis.com/maps/api/place/details/json';
  const url = `${baseUrl}?place_id=${placeId}&key=${apiKey}`;
  const response = UrlFetchApp.fetch(url);
  const json = JSON.parse(response.getContentText());

  if (json.result && json.result.geometry && json.result.geometry.location) {
    return json.result.geometry.location;
  }
  return null;
}

function fetchCoordinatesByName(name, apiKey) {
  try {
    const baseUrl = 'https://maps.googleapis.com/maps/api/geocode/json';
    const url = `${baseUrl}?address=${encodeURIComponent(name)}&key=${apiKey}`;
    const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
    const json = JSON.parse(response.getContentText());

    if (json.results && json.results[0] && json.results[0].geometry && json.results[0].geometry.location) {
      return json.results[0].geometry.location;
    }
  } catch (e) {
    Logger.log(`Error while requesting the name: ${name} - ${e.message}`);
  }
  return null;
}