Google Sheet Script Editor - setValues for array

Question

I`m trying to replace old values with new values using setValues in Google sheet script.

The data is in the below link...

https://docs.google.com/spreadsheets/d/1pSUVkxM9FhSNgizedHbY2MnYGTnC2iiYLfrWsoPmDks/edit?usp=sharing

I`m basically trying to remove first 14 characters and the last 12 characters under "Tracker" column

Below is the code I tried..

function URLReplacement() {
  var ss =  SpreadsheetApp.getActive().getSheetByName("transformer");
  var rng = ss.getRange("G:G");
  var data = rng.getValues();
  for (var items in data)
  {
    var newer = data[items][0].substring(14)
    // Turn these strings into an array
    var newerr = newer.split(" ")
    // Turn this into 2 dimensional array to use setValues

    ss.getRange("G:G").setValues([newerr])

  Logger.log([newer]);
}
}

But now, I get errors with the setValues statement Saying the range I set there do not match the data

What am I doing wrong here..?

Can anyone please provide me with suggestions / advice?

Tanaike · Accepted Answer · 2019-04-05 23:31:18Z

1

You want to convert from IMAGE_SUFFIX_"http://google.com"<xxxnouse>" to http://google.com at the column "G".
The format of IMAGE_SUFFIX_"http://google.com"<xxxnouse>" is constant.

If my understanding is correct, how about this modification? The reason of your error is that [newer] is not 2 dimensional array for using setValues(). If this error was removed, the header is removed by overwriting the empty value. So I would like to modify as follows.

Modification points:

When getLastRow() is used, the data size retrieved by it can be reduced from that retrieved by "G:G". By this, the process cost can be reduced.
Header is not retrieved by getRange(2, 7, ss.getLastRow(), 1).
From the format of IMAGE_SUFFIX_"http://google.com"<xxxnouse>", split() was used for parsing this value.
The converted data was put by setValues(). By this, the process cost can be also reduced.

Modified script:

function URLReplacement() {
  var ss =  SpreadsheetApp.getActive().getSheetByName("transformer");
  var rng = ss.getRange(2, 7, ss.getLastRow(), 1); // Modified
  var data = rng.getValues();
  var convertedData = data.map(function(e) {return e[0] ? [e[0].split('"')[1]] : e}); // Added
  rng.setValues(convertedData); // Added
}

Note:

In your shared sample Spreadsheet, the sheet name is "Sheet1". But your script uses "transformer" as the sheet name. Please be careful this.
If the format of actual values in your Spreadsheet is different from your shared Spreadsheet, this might not be able to be used.

References:

If this was not the result you want, I apologize.

answered Apr 5, 2019 at 23:31

Tanaike

203k12 gold badges126 silver badges222 bronze badges

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

3 Comments

StarSpirit_2000 Over a year ago

Hey Tanaike, This is doing exactly what I intended this to be. Apologies for the sheet name! Totally forgot about this.. Thank you so much!!!!!

Tanaike Over a year ago

@Jason1993 Thank you for replying. I'm glad your issue was resolved.

StarSpirit_2000 Over a year ago

Yup Thanks again :)

Collectives™ on Stack Overflow

Google Sheet Script Editor - setValues for array

1 Answer 1

Modification points:

Modified script:

Note:

References:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

Note:

References:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related