1

Trying to work with Sheets.Spreadsheets.Get and Sheets.Spreadsheets.Batchupdate. I'm trying to get pull formatting from one spreadsheet and paste that formatting to another. This is simply a proof of concept for further application. I get a JSON payload error with the following code and can't see to figure out how to format it to insert the Array.


function Test() {
 //sheets[].data[].rowData[].values[].cellData.effectiveFormat.backgroundColor
 var TestArray = Sheets.Spreadsheets.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg", {
   ranges:"Awesome!A1:C3",
   fields:"sheets(data(rowData(values(effectiveFormat.backgroundColor))))"
 });
 
var spreadsheetId = "1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg";
 var result = Sheets.Spreadsheets.batchUpdate({
   requests: [{
     updateCells: {
       rows: [{
         values: [{
           userEnteredValue: {
             stringValue: 'Test String'
           }, userEnteredFormat: {
               backgroundColor: TestArray
             }
         }]
       }],//rows
       fields: 'userEnteredValue.stringValue,userEnteredFormat.backgroundColor',
       start: {
         sheetId: 1616717220,
         rowIndex: 0,
         columnIndex: 0
       }
     }//update cell
   }]//requests
 }, spreadsheetId)
}  ```

**EDIT:**

Rebuilt function copying both Text and Background colors. 

function myFunction() {

var TestArray = Sheets.Spreadsheets.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg", {
   ranges:"Awesome!A1:C3",
   fields:"sheets(data(rowData(values(effectiveFormat.backgroundColor))))"
 });
 
 var backgroundColors = TestArray["sheets"][0]["data"][0]["rowData"]
                      .map(row => row["values"]
                      .map(value => value["effectiveFormat"]["backgroundColor"]));
 
 var TotalText = Sheets.Spreadsheets.Values.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg", "Awesome!A1:C3").values; 
 
//Map Text
var textrows = TotalText.map(rowText => {
 return {
   values: rowText.map(cellText => {
     return {
       userEnteredValue: {
         stringValue: cellText         
       }
     }       
   })
 }
})

//Map Background Colors
var colorrows = backgroundColors.map(rowColors => {
 return {
   values: rowColors.map(cellColor => {
     return {
       userEnteredFormat: {
         backgroundColor: cellColor        
       }       
     }             
   })
 }
})

var spreadsheetId = "1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg";
var result = Sheets.Spreadsheets.batchUpdate({
  requests: [{
    updateCells: {
      rows: textrows,
      fields: 'userEnteredValue.stringValue',
      start: {
        sheetId: 1616717220,
        rowIndex: 0,
        columnIndex: 0
      }
    }//update cell
  },{
    updateCells: {
      rows: colorrows,
      fields: 'userEnteredFormat.backgroundColor',
      start: {
        sheetId: 1616717220,
        rowIndex: 0,
        columnIndex: 0
      }
    }
  }]
}, spreadsheetId)
}


Edit #2:

   function myFunctionOneRequest() {


var TestArray = Sheets.Spreadsheets.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg", {
   ranges:"Awesome!A1:C3",
   fields:"sheets(data(rowData(values(effectiveFormat.backgroundColor))))"
 });
 
 var backgroundColors = TestArray["sheets"][0]["data"][0]["rowData"]
                      .map(row => row["values"]
                      .map(value => value["effectiveFormat"]["backgroundColor"]));
 
 var TotalText = Sheets.Spreadsheets.Values.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg", "Awesome!A1:C3").values; 

 
//Map Text
var textrows = TotalText.map((rowText,i) => {
 return {
   values: rowText.map((cellText,j) => {
     return {
       userEnteredValue: {
         stringValue: cellText         
       }
     }       
   })
 }
})

//Map Background Colors
var colorrows = backgroundColors.map((rowColors,k) => {
 return {
   values: rowColors.map((cellColor,l) => {
     return {
       userEnteredFormat: {
         backgroundColor: cellColor        
       }       
     }             
   })
 }
})

var spreadsheetId = "1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg";
var result = Sheets.Spreadsheets.batchUpdate({
  requests: [{
    updateCells: {
      rows: textrows,
      fields: 'userEnteredValue.stringValue',
      start: {
        sheetId: 1616717220,
        rowIndex: 0,
        columnIndex: 0
      }
    }//update cell
  }]
}, spreadsheetId)
}
LANCE
  • 111
  • 11
  • A shot in the dark... I'd try to remove the comments from the json. – Yuri Khristich Oct 04 '20 at 22:51
  • Hi, I posted an answer regarding this. Let me know if you need more pointers regarding how to build the request body for your batchUpdate after retrieving the `backgroundColors` for each cell in the requested range. – Iamblichus Oct 05 '20 at 11:04

1 Answers1

0

Issue:

You are supplying at Spreadsheet resource (TestArray, returned by spreadsheets.get) where you should provide a color. Hence, you are getting an invalid JSON payload error.

This is because the fields parameter will filter which nested fields will be populated in the response of your first call, but these nested fields will still be nested on your JSON, and you'll have to access them by specifying the corresponding parent properties.

Solution:

The response to your first call is something like:

{
  "sheets": [
    {
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "effectiveFormat": {
                    "backgroundColor": {
                      "red": 1,
                      "green": 1,
                      "blue": 1
                    }
                  }
                },
                // Other cells in row
              ]
            },
            // Other rows in the requested range
          ]
        } // Only one range is specified, so there's only one GridData element
      ]
    },
    // Other sheets
  ]
}

So, for example, if you want to access the backgroundColor of the first cell of the first row in the requested range, you should do the following:

var backgroundColor = TestArray["sheets"][0]["data"][0]["rowData"][0]
                                 ["values"][0]["effectiveFormat"]["backgroundColor"];

Or, alternatively, if you want to retrieve a 2D array of the backgroundColors of all the cells in the requested range, you could do this:

var backgroundColors = TestArray["sheets"][0]["data"][0]["rowData"]
                       .map(row => row["values"]
                       .map(value => value["effectiveFormat"]["backgroundColor"]));

If you want to update several cells, you would need to edit the request body accordingly, adding the additional rows and values to the corresponding arrays.

Edit:

For example, if you want the destination cells to have the same background colors as the source, and all of them to have the value Test String, you could build your request body like this:

var rows = backgroundColors.map(rowColors => {
  return {
    values: rowColors.map(cellColor => {
      return {
        userEnteredValue: {
          stringValue: 'Test String'         
        }, 
        userEnteredFormat: {
          backgroundColor: cellColor        
        }       
      }             
    })
  }
})
var result = Sheets.Spreadsheets.batchUpdate({
   requests: [{
     updateCells: {
       rows: rows,
       fields: 'userEnteredValue.stringValue,userEnteredFormat.backgroundColor',
       start: {
         sheetId: 1616717220,
         rowIndex: 0,
         columnIndex: 0
       }
     }//update cell
   }]//requests
}, spreadsheetId)

If each cell should have different string values, you should store those in a 2D array, and provide them inside the map methods, instead of Test String, specifying the corresponding indexes (provided as an optional parameter in each map).

Edit 2:

In order to update both values and background colors with the same request, you can just iterate through one of them with map, and use the corresponding index parameters (they are optional parameters of the map method, called i and j in the sample below) to access the different values of the other one.

For example, if backgroundColors and strings the 2D arrays which you want to use to build rows, you can do this:

var backgroundColors = [["2D array with colors"]];
var strings = [["2D array with strings"]];
var rows = backgroundColors.map((rowColors,i) => {
  return {
    values: rowColors.map((cellColor,j) => {
      return {
        userEnteredValue: {
          stringValue: strings[i][j]       
        }, 
        userEnteredFormat: {
          backgroundColor: cellColor        
        }       
      }             
    });
  }
});
Iamblichus
  • 18,540
  • 2
  • 11
  • 27
  • Thank you for the replay. "if you want to retrieve a 2D array of the backgroundColors of all the cells in the requested range" - I believe this is exactly the direction I needed. I'm unfamiliar with .map and how it work. Now I've got some reading and testing to do! – LANCE Oct 05 '20 at 23:02
  • @lamblichus - Some guidance on building the request body using backgroundColors would be great. – LANCE Oct 06 '20 at 18:00
  • @LANCE I edited my answer with a sample of how to build the request body in order to copy the background colors from the source range. Let me know if this solves your issue. – Iamblichus Oct 07 '20 at 12:42
  • @LANCE Also, let me know if you have problems setting the string values to the destination range. – Iamblichus Oct 07 '20 at 14:26
  • @Lamblichus - Thanks for the assistance. Using your code I've improved my understanding of the .map fxn. I've added an edit to my original question with the rebuilt code. I mapped out the text array and then color array separately. I've used one batch request but needed two separate requests. Is this the most efficient way to do this? This is a big step for me and I'm quite proud of the progress. Again, thank you for all the help. – LANCE Oct 07 '20 at 19:00
  • @LANCE You can do it in a single request. Just provide the index argument for each map (e.g. `backgroundColors.map((rowColors,i) => {...} `, `rowColors.map((cellColor,j) => {...}`), and provide those indexes when specifing `stringValue`. For example, if `2D_Strings` is the variable with which you stored the different text values, use `stringValue: 2D_Strings[i][j]`. – Iamblichus Oct 08 '20 at 13:06
  • @Lamblichus - I'd appreciate a little more guidance on this step. I've indexed both of the Varialbles to store color and Text as you've suggested. I don't understand how to adjust my request in incorporate both the colorrows and textrows in the "rows:" section of the request. I've attached my current version of the code as Edit #2 at the bottom of the original question. – LANCE Oct 12 '20 at 18:45
  • @LANCE I edited my answer again (see `Edit 2`) in order to show you more explicitly how to do this. – Iamblichus Oct 13 '20 at 14:28
  • The code works. I appreciate all your guidance throughout this. Just trying to understand the code completely. You are mapping the Backgroundcolors Array and then 'stringValue: 2D_Strings[i][j]' applies these indexes to the String Array? I tested by swapping and mapping the Strings and applying the indexes to the BackgroundColors. This can always be interchanged? – LANCE Oct 13 '20 at 17:35
  • @LANCE Yes this can be interchanged. The indexes i,j work for both 2D arrays. – Iamblichus Oct 14 '20 at 07:07
  • @lamblichus - I've attempted to expand the range of my initial array and have run into this error. 'TypeError: Cannot read property 'backgroundColor' of undefined' It occurs with this line of code -> '.map(value => value["effectiveFormat"]["backgroundColor"]));'. After some research I think this is because the array isn't completely full. Despite the fact that I'm using an array of background colors it occurs if there are empty cells but not if every cell in range has text in it. Is there some error checking to deal with an empty value? Why is the a lack of text a concern? – LANCE Oct 19 '20 at 00:57
  • @LANCE Since your original question got solved, I'd suggest you to post a new one to address this new issue you're facing. – Iamblichus Oct 19 '20 at 07:04