CODEWITHSUNDEEP
csvgoogle-drive-api
Bruno Desprez
Bruno Desprez

Reputation: 98

Google Drive API v3 - Import CSV in Sheet - Is it possible to disable smart conversion?

Thanks to the API, we can create an empty Sheet file and then fill it with CSV data. In my CSV data, strings are surrounded by double quotes ("). In these data, there are barcodes like "0212345678901". In the Sheet file, the string 0212345678901 is converted in number, so the first 0 disapear … That is not correct. In a CSV file, if a string is surrounded by double quotes, it shoul'd remain a string …

My data are imported in 2 steps :

  1. I create a new empty Sheet file with API. The API return the created file id.

    1. POST => https://www.googleapis.com/drive/v3/files

    2. body :

      {
  'name': 'aFilename',
  'mimeType': 'application/vnd.google-apps.spreadsheet',
  'parents': [idFolder]
}

  2. I update the file (with a PATCH REST call) with the previous returned id. The content is my CSV data.

    1. PATCH => https://www.googleapis.com/upload/drive/v3/files/${file['id']}?uploadType=media
    2. Headers : Authorization + 'Content-Type' : 'text/csv'

So, I have two questions : 1 : Is it possible to disable the smart conversion? 2 : Or, is there a way to specify that a string must remain a string (like in Excel if we start a formula with a simple quote ')?

Sample : 

"21/12/2018 17:02:06","3614950268815",1  
"21/12/2018 17:04:28","0212345005507",1  
"21/12/2018 17:32:10","3614950268891",1  
"21/12/2018 17:32:52","3614950268099",1  
"21/12/2018 17:34:36","3614950268105",1

Upvotes: 1

Views: 606

Answers (1)

Tanaike
Tanaike

Reputation: 201643

How about these modifications? There are 3 patterns in your situation. I think that there are several answers. So please think of this as one of them.

1. Add an apostrophe

In this pattern, by adding an apostrophe to the CSV data, it can be used as a string value. Please add an apostrophe to the CSV data and update the Spreadsheet using the modified CSV file. I think that this is most simple in these 3 patterns.

"21/12/2018 17:04:28","'0212345005507",1

or

"21/12/2018 17:04:28",'0212345005507,1

2. Use a method of spreadsheets.values.update in Sheets API

In this pattern, a method of spreadsheets.values.update in Sheets API is used to the created Spreadsheet.

Endpoint: 
PUT https://sheets.googleapis.com/v4/spreadsheets/### spreadsheetId ###/values/### sheetName ###?valueInputOption=RAW
Request body: 
{
 "values": [
  [
   "21/12/2018 17:02:06",
   "0212345005507",
   "1"
  ]
 ]
}

3. Use a method of spreadsheets.create in Sheets API

In this pattern, a method of spreadsheets.create in Sheets API is used. In this case, both creating spreadsheet and putting values can be done by one API call.

Endpoint: 
POST https://sheets.googleapis.com/v4/spreadsheets
Request body: 
{
 "sheets": [
  {
   "data": [
    {
     "rowData": [
      {
       "values": [
        {
         "userEnteredValue": {
          "stringValue": "21/12/2018 17:02:06"
         }
        },
        {
         "userEnteredValue": {
          "stringValue": "0212345005507"
         }
        },
        {
         "userEnteredValue": {
          "numberValue": 1
         }
        }
       ]
      }
     ]
    }
   ]
  }
 ],
 "properties": {
  "title": "### filename of spreadsheet ###"
 }
}

Note:

  • In the case of patterns 2 and 3, Sheets API puts the value of 0212345005507 by automatically adding an apostrophe. When you see the cell, it's '0212345005507.

References:

If these were not the result you want, I'm sorry.

Upvotes: 1

Related Questions