Convert Array into Object and paste values into a different column

Question

I have a column that contains a text like this on each row (text is different) - ABC1 London - ACompany: Ground Floor, Main Tower, 127 Bridge Street, London AB1 1JZ.

The script I have extracted the city name from that text and store it into a new array.

When I try to write those values into a new column I get Cannot convert Array to Object[][]. (line 18, file "Code").

I have tried to loop through that array again, but it returns 101 arrays (I have 101 rows) so when I set cities[0].length as width it pastes it on 101 column.

All I need is to get the city name from the text and paste it on the same sheet to a different column - Ex. the text is on column B12 and I want to paste just the city name into R12 - hope it makes sense.

Please see below the code I have

function getCity() {
  var ss = SpreadsheetApp.openById('ID');
  var sh = ss.getSheetByName('Response Data');
  var range = sh.getDataRange();
  var values = range.getValues();
  var cities = [];
  var newCity = []
  for (var i=1;i<values.length;i++)
  {

    var cellText = values[i][11];
    if(typeof cellText === 'number') {continue;};
    cities.push(cellText.substring(5, (cellText + " -").indexOf("-") - 1));
  }

  Logger.log(cities);

  sh.getRange(2, 18, cities.length, 1).setValues(cities);


}

Thank you!

Any errors that you are getting?

Parag Jadhav
– Parag Jadhav

2017-09-13 10:01:39 +00:00
Commented Sep 13, 2017 at 10:01 — Parag Jadhav
– Parag Jadhav, Commented Sep 13, 2017 at 10:01
Cannot convert Array to Object[][]. (line 18, file "Code")

Andrei K
– Andrei K

2017-09-13 10:02:54 +00:00
Commented Sep 13, 2017 at 10:02 — Andrei K
– Andrei K, Commented Sep 13, 2017 at 10:02

Parag Jadhav · Accepted Answer · 2017-09-13 10:32:00Z

1

Try this code and see if you're getting the same error,

function getCity() {
  var ss = SpreadsheetApp.openById('ID');
  var sh = ss.getSheetByName('Response Data');
  var range = sh.getDataRange();
  var values = range.getValues();
  var cities = [];
  var newCity = []
  for (var i=1;i<values.length;i++)
  {
    var cellText = values[i][11];
    if(typeof cellText === 'number') {continue;};
    cities.push([cellText.substring(5, (cellText + " -").indexOf("-") - 1)]);
  }
  Logger.log(cities);
  sh.getRange(2,18,cities.length,1).setValues(cities);
}

edited Sep 13, 2017 at 10:32

answered Sep 13, 2017 at 10:05

Parag Jadhav

1,8992 gold badges26 silver badges42 bronze badges

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

5 Comments

Andrei K Over a year ago

I get Incorrect range height, was 1 but should be 101 (line 15, file "Code")

Parag Jadhav Over a year ago

Share sample spreadsheet with sample data, there is something wrong .getRange(2, 18, cities.length, 1) with this part

Andrei K Over a year ago

docs.google.com/spreadsheets/d/…

Andrei K Over a year ago

It does! :) Thank you very much .... need to push more and learn arrays :)

Parag Jadhav Over a year ago

All the best :)

Collectives™ on Stack Overflow

Convert Array into Object and paste values into a different column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related