Sheets API formatting header

Question

I'm using firebase cloud functions in order to add and edit data in an existing spreadsheet.

The first operation I do to the sheet is adding a first row that contains the columns names. After that I change the format of the first inserted row to be in bold. That succeeded but then, every row of data I append to the sheet appear bold as well.

Here is a snippet of my code:

header = [/* array of the columns names */];

 await appendPromise({
        spreadsheetId: spreadsheetID,
        range: 'Sheet1',
        valueInputOption: 'USER_ENTERED',
        insertDataOption: 'INSERT_ROWS',
        resource: {
          values: [header],
        },
      });
      
      
 return updateBatchPromise(spreadsheetID,
     {"requests": [
    {"repeatCell": {
        "range": {
            "sheetId": 0,
            "startRowIndex": 0,
            "endRowIndex": 1
        },
        "cell": {
            "userEnteredFormat": {
                "textFormat": {
                    "bold": true
                }
            }
        },
        "fields": "userEnteredFormat.textFormat.bold"
    }}
]}
      )



/*...... HELPER FUNCTIONS........*/

function appendPromise(requestWithoutAuth) {
  return new Promise((resolve, reject) => {
    google.auth.getClient({
    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
  }).then((client) => {
      const sheets = google.sheets('v4');
      const request = requestWithoutAuth;
      request.auth = client;
      return sheets.spreadsheets.values.append(request, (err, response) => {
        if (err) {
          console.log(`The API returned an error: ${err}`);
          return reject(err);
        }
        return resolve(response.data);
      });
    });
  });
}



function updateBatchPromise(spreadsheetId, res) {
  return new Promise((resolve, reject) => {
    google.auth.getClient({
    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
  }).then((client) => {
      const sheets = google.sheets('v4');
      return sheets.spreadsheets.batchUpdate({
  auth: client,
  spreadsheetId: spreadsheetId,
  resource: res
}, (err, response) => {
        if (err) {
          console.log(`The API returned an error: ${err}`);
          return reject(err);
        }
        return resolve(response.data);
      });
    });
  });
}

The first picture is the requested behavior and the second is what I got.

Nimantha · Accepted Answer · 2021-11-05 04:58:05Z

Answer:

After you have appended the first row of data you then need to specify the rest of the sheet should not be bold.

More Information:

When you append data to a Spreadsheet, the newly appended data takes on the same format as the line above it - you can think of it as copying data into a new row which takes on the properties of the row above it, rather than updating the row and using the text formatting already set.

You can get around this by adding an extra batch request which specifies that the other lines should not be bold.

This is done by omitting the endRowIndex option and setting startRowIndex to 1.:

{
  "requests": [
    {
      "repeatCell": {
        "range": {
          "sheetId": 0,
          "startRowIndex": 1
        },
        "cell": {
          "userEnteredFormat": {
            "textFormat": {
              "bold": false
            }
          }
        },
        "fields": "userEnteredFormat.textFormat.bold"
      }
    }
  ]
}

This operation does however have to be done after the first row has been appended, as otherwise the row will continue to copy the bolded formatting from the first line.

The order of requests should be as follows:

spreadsheets.batchUpdate request to make the first row bold
spreadsheets.values.append request to add the headers
spreadsheets.values.append request to add the first set of data
spreadsheets.batchUpdate request to update rows 2 and down so to not copy over the bold text formatting.

References:

Tommaso Macchioni · Accepted Answer · 2024-04-09 16:40:04Z

0

With one API call:

{
  "requests": [
    {
      "repeatCell": {
        "range": {
          "sheetId": XXXXX,
          "startRowIndex": 0,
          "endRowIndex": 1
        },
        "cell": {
          "userEnteredFormat": {
            "textFormat": {
              "bold": true
            }
          }
        },
        "fields": "userEnteredFormat.textFormat.bold"
      }
    }
  ]
}

answered Apr 9, 2024 at 16:40

Tommaso Macchioni

11 bronze badge

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Sheets API formatting header

2 Answers 2

Answer:

More Information:

References:

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Answer:

More Information:

References:

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related