CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Daniel
Daniel

Reputation: 23

Exception: The number of columns in the data does not match the number of columns in the range. - Issue

    function pricing() {
      convert_txt_gsheets();
      lastrow2();
    }
    
    function convert_txt_gsheets() {
      var source = SpreadsheetApp.openById('ID1').getSheetByName('Daily Report');
      var targetrange = source.getRange(2, 1, source.getLastRow(), source.getLastColumn());
      targetrange.clear();
    
      var file = DriveApp.getFileById('ID2');
      var body = file.getBlob().getDataAsString().split(/\r/);
      var result = body.map(split(/|/))
        // vvv
        .map(row => row.map(cell => cell.replaceAll(`"`, ``)));
      SpreadsheetApp.getActive().getSheetByName('Daily Report').getRange(1, 1, result.length, result[0].length).setValues(result);
      return;
    }



function lastrow2() {
var source = SpreadsheetApp.openById('ID1').getSheetByName('Daily Report');
var target = SpreadsheetApp.openById('ID1').getSheetByName('Permanent Record');
var target = target.getRange(target.getLastRow()+1, 1, source.getLastRow(), source.getLastColumn());
var rangeValues = source.getRange(2, 1, source.getLastRow(), source.getLastColumn()).getValues();
target.setValues(rangeValues); 
}

Could I ask for help with the above code? The function Lastrows2 works properly. However, the Convert_txt_gsheets throws exception "Exception: The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 9. convert_txt_gsheets" which terminates the whole thing, and stops lastrow2 from ever engaging.

This is the example data that is being pulled from txt.

COLUMN0 COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7 COLUMN8
"Rocketship" "5.99" "5.39" "5.39" "" "5.39" "5.39" "7.5.2022" "william"

Edit I modified the example and the original code. The error currently received is

ReferenceError: split is not defined

Upvotes: 0

Views: 98

Answers (1)

Waxim Corp
Waxim Corp

Reputation: 917

You are almost good, the problem is here :

  var result = body.map(r => r.split(/,/))
    // vvv
    .map(row => row.map(cell => cell.replaceAll(", ``)));

Here the function I did and tested :

function cleantext() {

  var text = `COLUMN0,COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6,COLUMN7,COLUMN8
"Rocketship","5.99","5.39","5.39","","5.39","5.39","7.5.2022","william"
"WobblyHouse","3.99","3.49","3.49","","3.49","3.49","7.5.2022","billiam"`;

  var cleaned = text.split('\n').map(line => line.split(',').map(cell => cell.replace(/["]/g, "")));

  Logger.log(cleaned);
}

// [[COLUMN0, COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COLUMN6, COLUMN7, COLUMN8], 
// [Rocketship, 5.99, 5.39, 5.39, , 5.39, 5.39, 7.5.2022, william], 
// [WobblyHouse, 3.99, 3.49, 3.49, , 3.49, 3.49, 7.5.2022, billiam]]

Upvotes: 1

Related Questions