Error in Combining Columns from sheets

[batoolk]

I am working on importing an Excel workbook that has multiple sheets. The columns' types in the sheets were set to 'General' in Excel by default, so I changed them manually in Excel from General-type to Text-type except for one column that was changed into Number-type because its head was zeros and when I import it in R, it is dropped automatically if it's character, text, or general.
The task at hand is to combine the sheets in one data frame, and here is the code I am working on:

library(tidyverse)
library(readxl)
library(purrr)
library(ggplot2)
library(dplyr)

CombineSheets <- function(excel_file_path){
  df <- excel_file_path %>%
          excel_sheets() %>% #creates a character vector of the sheets' names
          set_names() %>% #sets the names of each element in the character vector to itself
          map_dfr(.f = ~read_excel(path = excel_file_path, sheet = ..1), .id = "sheet")
  return(df)}

CombineSheets("C:\\Users\\Batool\\Documents\\ExcelFiles\\ConfigList.xlsx")

However, I am getting the error below although I have both columns set to the same type (Text) in Excel. So where could the issue be?
I thought about using R to search for the column of the data type to be Number and change it after importing the data set. However, by the time I import the sheet, the column would have been dropped. Any ideas or solution, please?

Error message: Error: Can't combine LWFM_W$PGN <character> and LWFM_N$PGN <double>.

[FJCC]

In a spreadsheet, changing the cell format after data are entered will not change the data type of the existing data. If cell A1 has the General format and you enter 123 in it and the formula =ISNUMBER(A1) in B1, cell B1 will display TRUE. Changing the format of A1 to text will have no effect; B1 will still display TRUE.
The read_excel() function has a col_types argument that you should be able to use set the data in the imported data frame.

[batoolk]

Correct, but the number of columns in each of the datasheets that I have varies in length, so I would have to write a vector for each sheet that specifies the column type and assign it to col_type . This can be done easily for a single workbook, but in my case I would have to apply this for multiple workbooks automatically.

[FJCC]

It might work to set all of the imported columns to the type character and then change some of them to numeric later. That will be easier if there is a pattern to the column names or positions that have to be converted.