CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
StarSpirit_2000
StarSpirit_2000

Reputation: 125

Google Sheet Script Editor - setValues for array

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?

Upvotes: 1

Views: 452

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • 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.

Upvotes: 1

Related Questions