Google Sheets Script

[Josef_Winstone]

Hi guys,

Totally new here, moved from google sheets today.

I’m looking to build a restaurant delivery app with an Airtable backend.

I currently have a script I made on GS that pulls an address from one cell & calculates it’s distance in km to a predetermined address in the script (restaurant address).

To what extent can I just copy this script over?

Haven’t seem to make it work yet!

Thanks in advance,
Josef

[Bill_French]

Welcome to the community @Josef_Winstone!

Josef_Winstone:

To what extent can I just copy this script over?

Are you asking if your Google Apps Script code can be copied into an Airtable Script Block?

If so, it depends on what features in Google Apps Script were used and how it was crafted. If you share the code here, you’ll likely have more success in finding out if it’s compatible.

[Josef_Winstone]

Thanks!

Here’s script 1 (the distance):

/**

• Get the distance between 2 different addresses.

• @ param {string} origin_address The origin/start address as string Eg. “102 Petty France, London, SW1H 9AJ”.

• @ param {string} destination_address The destination/end address as string Eg. “10 Whitechapel High Street, London, E1 8QS”.

• @ param {string} travel_mode The mode of travel as string. Default: DRIVING. Options: BICYCLING, TRANSIT, WALKING.

• @ param {string} return_type The return type as string. Default: MILES. Options: KILOMETERS, MINUTES, HOURS, STEPS.

• @ return the distance between 2 different addresses.

• @ customfunction
  */
  function GOOGLEDISTANCE(origin_address,destination_address,travel_mode,return_type) {
  Utilities.sleep(1000);

  var travelMode = “”;

  switch(travel_mode) {
  case “BICYCLING”:
  case “bicycling”:
  travelMode = Maps.DirectionFinder.Mode.BICYCLING;
  break;
  case “DRIVING”:
  case “driving”:
  travelMode = Maps.DirectionFinder.Mode.DRIVING;
  break;
  case “TRANSIT”:
  case “transit”:
  travelMode = Maps.DirectionFinder.Mode.TRANSIT;
  break;
  case “WALKING”:
  case “walking”:
  travelMode = Maps.DirectionFinder.Mode.WALKING;
  break;
  default:
  // Default to driving
  travelMode = Maps.DirectionFinder.Mode.DRIVING;
  //return “Error: Wrong travel mode”;
  }

  // var auth = Maps.setAuthentication(clientId, signingKey);

  var directions = Maps.newDirectionFinder()
  .setRegion(‘UK’)
  .setLanguage(‘en-GB’)
  .setOrigin(origin_address)
  .setDestination(destination_address)
  .setMode(travelMode)
  .getDirections();

  if (directions.status !== “OK”)
  return “No distance available. You’re probably too far away!”

  var route = directions.routes[0].legs[0];
  var time = route.duration.value;
  var distance = route.distance.value;

  var steps = route.steps.map(function(step) {
  return step.html_instructions.replace(/<[^>]+>/g, “”);
  }).join("\n");

  switch(return_type) {
  case “MILES”:
  case “miles”:
  return distance * 0.000621371;
  break;
  case “KILOMETERS”:
  case “kilometers”:
  return distance / 1000;
  break;
  case “MINUTES”:
  case “minutes”:
  return time / 60;
  break;
  case “HOURS”:
  case “hours”:
  return time / 60 / 60;
  break;
  case “STEPS”:
  case “steps”:
  return steps;
  break;
  default:
  // Default to miles
  return distance * 0.000621371;
  //return “Error: Wrong return type”;
  }

And script 2 (copying formula with new entries):

function fillFormula() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(‘User profile’);
var lr = sheet.getLastRow();
var fillDownRange = sheet.getRange(lr, 7, 1);
sheet.getRange(“G”+(lr-1)).copyTo(fillDownRange);
}

Thanks in advance,
Josef

[Bill_French]

Josef_Winstone:

Utilities.sleep(1000);

I’ll start with this simple script feature. This is Google Apps Script specific and will not run in a Script Block. Furthermore, Google Apps Script is a non-blocking script environment (unless you transition to the V8 runtime engine). In contrast, Airtable’s script blocks are non-blocking so the vast elements of your function will require some transitions.