[SOLVED] Google Sheets Auto HyperLink to Google Earth Web?

Google Sheets Auto HyperLink to Google Earth Web?

Was wondering if there's simple way to convert column which is full with addresses to hyperlink for Google Earth web.

If you try https://earth.google.com/web/search/New York it will show New York, which means that I need https://earth.google.com/web/search/ & self_cell_value somehow, of course the text of the hyperlink can be whatever the text (address) is inside the cell.

Any tips how to do this easy ? Arrayformula and stuff need to be placed onto another column/cells, which is something that I don't want. Just the column of addresses converted into hyperlinks as mentioned. Upon adding new addresses it should be automatically happening.

Thanks

Solution

This is a sample script that will trigger when column A in Sheet1 is edited, it will take the values and replace the space with +.

So https://earth.google.com/web/search/New York will change to https://earth.google.com/web/search/New+York automatically.

You can edit the column number or the sheet name as required.

function onEdit(e) {

  // get the range where the edit once done
  let range = e.range

  // get the column number where the changes is done
  let working_range = range.getColumn()

  // the Sheet name where the change is done
  let sheet = e.source.getSheetName(); 

  // if statement, if the change was done in Sheet1 
  //and column 1 (column A), the script will trigger
  if (sheet == 'Sheet1' & working_range==1){ 

      // get the range value in column A
      let cell_value = range.getValue().toString();

      // replace the space with a +
      let new_cell_value = cell_value.replace(' ', '+')

      //sets the hyperlink
      let richValue = SpreadsheetApp.newRichTextValue()
      .setText(new_cell_value)
      .setLinkUrl(new_cell_value)
      .build();
      range.setRichTextValue(richValue);
  }
}

This is how it works:

Update:

If you add, just one word instead of a URL, you can make this changes to the code to create the hyperlink:

function onEdit(e) {

  // get the range where the edit once done
  let range = e.range

  // get the column number where the changes is done
  let working_range = range.getColumn()

  // the Sheet name where the change is done
  let sheet = e.source.getSheetName(); 

  // if statement, if the change was done in Sheet1 
  //and column 1 (column A), the script will trigger
  if (sheet == 'Sheet1' & working_range==1){ 

      // get the range value in column A
      let cell_value = range.getValue().toString();

      // replace the space with a +
      let new_cell_value = cell_value.replace(' ', '+')

      //sets the hyperlink with the current value in the column 
      // and the URL to Google Earth withut the space 
      let richValue = SpreadsheetApp.newRichTextValue()
      .setText(cell_value)
      .setLinkUrl("https://earth.google.com/web/search/" + new_cell_value)
      .build();
      range.setRichTextValue(richValue);
  }
}

Reference: