Google Sheets automatically changing date format while setting data in sheet using Google APIs

user_13662850 0
Viewed 813

I am working on a project where I need to collect data from an API, store them in a matrix and set that data in a Google Sheet using the Google-Sheets-APIv4 with Node.js. Two key in that API are object.start_date and object.end_date which return date strings in the format for e.g "2021-09-22" ie. yyyy-mm-dd. I want to convert that date to dd/mm/yyyy format. The function I have implemented for the same is:

function changeDateFormat(dateString){
  let a=dateString.split('-');
  let t=a[0];
  a[0]=a[2];
  a[2]=t;
  //console.log(a.join('/'));
  return `${a.join('/')}`;
}

The code works fine and I have used it in the manner shown below:

 let checkin=`${changeDateFormat(`${tenancy.start_date}`)}`;
 console.log(checkin);
 let checkout=`${changeDateFormat(`${tenancy.end_date}`)}`;
 console.log(checkout);

tenancy in the code above is an object. The output that I get in the console is which is the desired output which is the desired format, which shows that algorithm for changing date is working fine too.

Also, my code for adding the data in the sheet is

await googleSheets.spreadsheets.values.append({
    auth,
    spreadsheetId,
    range:"MySheet!A2:AC",
    valueInputOption:"USER_ENTERED",
    resource: {values : newData} 
/*newData is a 2-D array where each row has several data fields in the form newData[i]=[propertyName,id,......,check-in,checkout,.......]*/
  });

However, when I add the data in the sheet, the date fields are entirely jumbled up where some have the desired dd/mm/yyyy format while others have the same yyyy-mm-dd format as shown in the image enter image description here

I am not being able to solve the problem as I cannot find any error in the code either but the output is completely jumbled as it's working in some cells but not working in other cells. What might be a possible workaround for this problem? I will gladly provide any other information or data regarding the problem or my code if needed. Thanks!

node.js google-sheets-api google-sheets
1 Answers

I believe your goal and your situation as follows.

  • You want to set the number format of the cells to dd/mm/yyyy.
  • You want to achieve this using googleapis for Node.js.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API.

Modification points:

  • For example, when the values of [["2021-01-02", "02/01/2021"]] are put to the Spreadsheet using the method of "spreadsheets.values.append" in Sheets API with valueInputOption of USER_ENTERED, each value is put as the date object. But it seems that the number format is different.
  • In order to use the same number format, in this answer, I would like to set the number format of dd/mm/yyyy to the cells using the method of "spreadsheets.batchUpdate".

When above points are reflected to your script, it becomes as follows.

Modified script:

Please set the value of sheetId.

await googleSheets.spreadsheets.values.append({
  auth,
  spreadsheetId,
  range: "MySheet!A2:AC",
  valueInputOption: "USER_ENTERED",
  resource: { values: newData },
});

const sheetId = "###";  // <--- Please set the sheet ID of "MySheet".
const resource = {
  auth,
  spreadsheetId,
  resource: {
    requests: [
      {
        repeatCell: {
          range: {
            sheetId: sheetId,
            startRowIndex: 1,
            startColumnIndex: 12,
            endColumnIndex: 15,
          },
          cell: {
            userEnteredFormat: {
              numberFormat: {
                pattern: "dd/mm/yyyy",
                type: "DATE",
              },
              horizontalAlignment: "RIGHT", // <--- Added
            },
          },
          fields: "userEnteredFormat",
        },
      },
    ],
  },
};
await googleSheets.spreadsheets.batchUpdate(resource);
  • When above modified script is run, the number format of the columns "M" to "O" is changed to dd/mm/yyyy. This is from your sample image.
  • If you want to modify the format and range, please modify above script.
  • And, at above script, the batchUpdate method reflects all rows of the columns "M" to "O". So I think that in this case, this batchUpdate method might be able to achieve your goal by only one time running.

References:

user_71086530
Related