Changing userEnteredFormat a second time is not applied

0
votes

I would like to import CSV data into a spreadsheet. I use German number formats with a dot as thousands delimiter and comma for fractions ( 1.000.000,00 ). My data source generates a column with a dot as fraction delimiter ( 3.3333333333 ). To handle that, i use the following process:

  1. set column format in the destination spreadsheet to text
  2. paste csv data
  3. search that column for "." and replace with ","
  4. set column format in the destination spreadsheet to number, pattern "0.0000"
  5. resize column width (just to check if my requests are still processed

In this process the fourth step is not applied. I've already split my request in two command chains, but that did not work either.

function readFile(id){
  var file=DriveApp.getFileById(id);
  var csv=file.getBlob().getDataAsString();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var resource = {
    requests: [
        {
            "repeatCell": {
              "range": {
                "sheetId": sheet.getSheetId(),
                "startRowIndex": 1,
                "startColumnIndex": 10,
                "endColumnIndex": 11
              },
              "cell": {
                "userEnteredFormat": {
                  "numberFormat": {
                    "type": "TEXT"
                  }
                }
              },
              "fields": "userEnteredFormat.numberFormat"
            }
  
        },
        {
            pasteData: {
                data: csv,
                coordinate: {sheetId: sheet.getSheetId()},
                delimiter: ",",
            }
        },
        {
            "findReplace": {
              "range": {
                "sheetId": sheet.getSheetId(),
                "startRowIndex": 1,
                "startColumnIndex": 10,
                "endColumnIndex": 11
              },
              find: ".",
              replacement: ","
            }
  
        }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
  var resource = {   1.
    requests: [
        {
            "repeatCell": {
              "range": {
                "sheetId": sheet.getSheetId(),
                "startRowIndex": 1,
                "startColumnIndex": 10,
                "endColumnIndex": 11
              },
              "cell": {
                "userEnteredFormat": {
                  "numberFormat": {
                    "type": "NUMBER",
                    "pattern": "0.0000"
                  }
                }
              },
              "fields": "userEnteredFormat.numberFormat"
            }
        },
        {
            "autoResizeDimensions": {
              "dimensions": {
                "dimension": "COLUMNS",
                "startIndex": 0
              }
            }
          }

    ]
  };
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
  return file.getName();
}

My data source looks like this:

Foo,Bar,Baz,...Headline does not matter...
AAA,BBB,CCC,,2020-09-03,14:30,2020-09-03,17:26,UTC+2:00,2:56,2.933333333333333,,,,
.... hundreds of similar lines

Problematic column is K or column index 10.

The columns are still resized. I can apply the number format right after the script run without a problem. I get no error or message while executing.

I tried to set format to "NUMBER_FORMAT_TYPE_UNSPECIFIED" first (i know that should not be used explicitly). In this case the check-mark in the format menu is set to automatic, but my column still contains no numbers to calculate with.

1
google-sheets-api

1 Answers

0
votes

Answer:

Your replacement of . to , is only happening from row 2 onwards.

Fix:

From the documentation:

GridRange

A range on a sheet. All indexes are zero-based. Indexes are half open, i.e. the start index is inclusive and the end index is exclusive -- [startIndex, endIndex). Missing indexes indicate the range is unbounded on that side.

So, your startRowIndex of findReplace.range should be 0:

{
    "findReplace": {
      "range": {
        "sheetId": sheet.getSheetId(),
        "startRowIndex": 0,
        "startColumnIndex": 10,
        "endColumnIndex": 11
      },
      find: ".",
      replacement: ","
    }  
}

References: