Search code examples
javascriptgoogle-sheets-api

Sheets API formatting header

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. enter image description here enter image description here

Solution

  • 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: