Google sheets api not able to set default format

1
votes

According to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#SpreadsheetProperties I'm supposed to be able to set defaultFormat already when I create the Google sheet to not have to apply it on all cells after adding my data.

It doesn't do anything though. I know that verticalAlignment exists because if I make a typo it complains about missing the property.

What can be the problem?

return new Promise((resolve, reject) => {
    const sheets = google.sheets({version: 'v4', auth});
    const request = {
        resource: {
          properties: {
            title: "This is the sheet title",
            defaultFormat: {
              "verticalAlignment": "TOP"
            }
          },
          sheets: [
            {
              properties: {
                title: "The first tab"
              }
            }
          ]
        },
        auth: auth,
    };
    sheets.spreadsheets.create(request, (err, response) => {
        if (err) {
            return reject(err);
        }
        resolve(response);
    });
});
1
node.jsgoogle-sheetsgoogle-sheets-api

1 Answers

3
votes
  • You want to create new Spreadsheet with {"defaultFormat": {"verticalAlignment": "TOP"}} using Sheets API.
  • You want to achieve this using googleapis with Node.js.
  • You have already been able to get and put values for Spreadsheet using Sheets API.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Issue and workaround:

I think that the reason of your issue might be that defaultFormat in SpreadsheetProperties is This field is read-only.. Ref

In order to create new Spreadsheet using {"verticalAlignment": "TOP"}, how about the following workarounds?

Pattern 1:

In this pattern, "verticalAlignment": "TOP" is used for the sheet using the method of "create". In this case, each cell is required to be set with { userEnteredFormat: { verticalAlignment: "TOP" } } like below.

Modified script:

return new Promise((resolve, reject) => {
  const sheets = google.sheets({ version: "v4", auth });
  let rowData = [];
  for (let r = 0; r < 1000; r++) {
    let row = [];
    for (let c = 0; c < 26; c++) {
      row[c] = { userEnteredFormat: { verticalAlignment: "TOP" } };
    }
    rowData.push({ values: row });
  }
  const request = {
    resource: {
      properties: { title: "This is the sheet title" },
      sheets: [
        {
          properties: { title: "The first tab" },
          data: { rowData: rowData }
        }
      ]
    }
  };
  sheets.spreadsheets.create(request, (err, response) => {
    if (err) {
      return reject(err);
    }
    resolve(response.data);
  });
});
  • In this case, the default sheet has 1,000 rows and 26 columns. So I used above for loops.

Pattern 2:

In this pattern, at first, new Spreadsheet is created, and then, "verticalAlignment": "TOP" is given with the method of "batchUpdate".

Modified script:

return new Promise((resolve, reject) => {
  const sheets = google.sheets({ version: "v4", auth });
  const request = {
    resource: {
      properties: { title: "This is the sheet title" },
      sheets: [{ properties: { title: "The first tab" } }]
    }
  };
  sheets.spreadsheets.create(request, (err, response) => {
    if (err) {
      return reject(err);
    }
    sheets.spreadsheets.batchUpdate(
      {
        spreadsheetId: response.data.spreadsheetId,
        resource: {
          requests: [
            {
              repeatCell: {
                range: {
                  sheetId: response.data.sheets[0].properties.sheetId
                },
                cell: {
                  userEnteredFormat: {
                    verticalAlignment: "TOP"
                  }
                },
                fields: "userEnteredFormat.verticalAlignment"
              }
            }
          ]
        }
      },
      function(err, response) {
        if (err) {
          return reject(err);
        }
        resolve(response.data);
      }
    );
  });
});

References:

If I misunderstood your question and this was not the direction you want, I apologize.