Using this App Script code,

function myFunction() {
  const sheetName = "Sheet1";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();

  for (var i = 2; i < lastRow  1; i  ) {
    var value = sheet.getRange(i,1).getValue();

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]
    var cell = sheet.getRange(i,2);
    cell.setValue(res);
    console.log(res);
    
  }
}

I am converting 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}]]

The input text is being converted into Output text accurately. In the console, I can see that it is working accurately.

The problem I am facing is that when I send Output text back to Google Sheets, I am not getting the exact output. It only shows part of the output. See image.

Please help with why I am not getting exact output in google sheet cell as in Console. Thank you Here is Google Sheets.

CodePudding user response：

In your script, I think that res is an object. I thought that this might be the reason for your current issue. In this case, it is required to convert it to the string. So, how about the following modification?

From:

cell.setValue(res);

To:

cell.setValue(JSON.stringify(res));

Note:

• In your script, getValue is used in a loop. In this case, the process cost will be high. So, as another approach, how about the following script?

  function myFunction2() {
    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];
    });
    sheet.getRange(2, 2, res.length).setValues(res.map(e => [JSON.stringify(e)]));
  }
  

Reference:

• JSON.stringify()