Format Text using App Script in Google sheet

Question

Using the App Script, I need to convert text:

Input:

POLYGON ((-111.705934405 32.76300316, -111.706191897 32.748205765, -111.706191897 32.748205765, -111.706191897 32.748205765, -111.688682437 32.748566707, -111.688939929 32.733261511, -111.655036807 32.732828307, -111.655294299 32.740914437, -111.645938754 32.740842243, -111.646024585 32.748783271, -111.654607654 32.74914421, -111.654092669 32.763291866, -111.705934405 32.76300316))

Output:

[[{"lat":32.763597994888265,"lng":-111.70779863788822},{"lat":32.763670170794725,"lng":-111.65269533588626},{"lat":32.74887288656241,"lng":-111.65346781208255},{"lat":32.74880069866226,"lng":-111.64479891254642},{"lat":32.741004061134866,"lng":-111.64548555805423},{"lat":32.741004061134866,"lng":-111.65424028827884},{"lat":32.73356774344866,"lng":-111.65466944172123},{"lat":32.732990139407825,"lng":-111.70608202411869}]]

Using App script. I have converted text using formulas. Here is That sheet. Polygon can have 200+ points. It is difficult to adjust those points in the formula.

And note that the latitude and longitude is reversed in the input.

Tanaike · Accepted Answer · 2023-01-28 00:22:44Z

2

In your situation, how about the following sample script?

Sample script:

const value = "POLYGON ((-111.705934405 32.76300316, -111.706191897 32.748205765, -111.706191897 32.748205765, -111.706191897 32.748205765, -111.688682437 32.748566707, -111.688939929 32.733261511, -111.655036807 32.732828307, -111.655294299 32.740914437, -111.645938754 32.740842243, -111.646024585 32.748783271, -111.654607654 32.74914421, -111.654092669 32.763291866, -111.705934405 32.76300316))";
const r1 = value.match(/\(\((.*)\)\)/);
const r2 = r1[1].split(",").map(e => {
  const [lng, lat] = e.trim().split(" ");
  return { lat: Number(lat), lng: Number(lng) };
});
const res = [r2];
console.log(res);

When this script is run, the following result is obtained.

  [[{"lat":32.76300316,"lng":-111.705934405},{"lat":32.748205765,"lng":-111.706191897},{"lat":32.748205765,"lng":-111.706191897},{"lat":32.748205765,"lng":-111.706191897},{"lat":32.748566707,"lng":-111.688682437},{"lat":32.733261511,"lng":-111.688939929},{"lat":32.732828307,"lng":-111.655036807},{"lat":32.740914437,"lng":-111.655294299},{"lat":32.740842243,"lng":-111.645938754},{"lat":32.748783271,"lng":-111.646024585},{"lat":32.74914421,"lng":-111.654607654},{"lat":32.763291866,"lng":-111.654092669},{"lat":32.76300316,"lng":-111.705934405}]]

Note:

If you want to retrieve the values from column "A" of your sample Spreadsheet, how about the following sample script? In this case, from your showing the expected value, each element of the result array is a 2-dimensional array. Please be careful about this.

function myFunction() {
  const sheetName = "Sheet1";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const values = sheet.getRange("A2:A" + sheet.getLastRow()).getValues();
  const res = values.map(([a]) => {
    const r1 = a.match(/\(\((.*)\)\)/);
    const r2 = r1[1].split(",").map(e => {
      const [lng, lat] = e.trim().split(" ");
      return { lat: Number(lat), lng: Number(lng) };
    });
    return [r2];
  });
  console.log(res);
}

Reference:

map()

answered Jan 28, 2023 at 0:22

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user8702720 Over a year ago

Thank you so much for the solution. The first one is working perfectly. Can you please check the second one? Can you please tell me how to put the result of the script back into Column B against each polygon?

Tanaike Over a year ago

@user8702720 Thank you for replying. I'm glad your issue was resolved. About Can you please tell me how to put the result of the script back into Column B against each polygon?, in this case, please use my 2nd script, and please replace console.log(res); with sheet.getRange(2, 2, res.length).setValues(res.map(e => [JSON.stringify(e)]));.

Collectives™ on Stack Overflow

Format Text using App Script in Google sheet

1 Answer 1

Sample script:

Note:

Reference:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

Note:

Reference:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related