CODEWITHSUNDEEP
node.jsgoogle-sheetsgoogle-sheets-api
Tod
Tod

Reputation: 8252

How to copy a Google sheet and get data validation to stay intact?

I have a Google spreadsheet template with two sheets, Data Entry and Data Validation. The Data Validation sheet has a number of columns with valid values for matching columns on the Data Entry sheet. Everything works as expected. I need to copy these two sheets to a Sheet Under Test (SUT). I am using the sheets API to copy both sheets. I copy the Data Validation sheet first and then the Data Entry sheet. Here's the code and this appears to work.

 const request =  {
    spreadsheetId :fromSpreadsheetId,
    sheetId : fromSheetId,
    resource:{
      destinationSpreadsheetId: toSpreadsheetId,
    },
  }
  const result = await  _sheetService.spreadsheets.sheets.copyTo(request)

On my SUT, both sheets appear and the Data entry sheet has all the expected dropdowns and they all have the proper values. Seems perfect. The problem is when you select an item from any drop down in any column it selects and enters the proper value and then adds a red triangle and the message that an invalid value has been entered. If the column has the rejection setting then the value is removed and the error dialog appears.

Image of Invalid Value Showing with valid drop down

The image shows two cells where I have already selected Video Course from the drop down.

If I go in and reselect column where I want validation, use Data→DataValidation… and just hit the Save button that column starts working, so it would appear everything came across correctly but the sheet doesn't think so. Is there any programmatic way to force the above process that I did manually? Is there something else I need to do in the sheets.copyTo method to make this work properly?

EDIT

This project is written in Node.js with a combination of TypeScript and JavaScript. The lower level code for talking to the Sheets API and copying the sheet between spreadsheets can be found in this github file. The method is copySheetFromTo and it's at the bottom of the file.

A sample source sheet with public view permissions

A sample destination sheet with public edit permissions

The integration test that used the above two files to copy the sheets is at the bottom of the file and has 'DEBUGGING TEST' at the beginning of the name (starts at line 209)

Upvotes: 1

Views: 1991

Answers (1)

Tanaike
Tanaike

Reputation: 201573

  • You want to copy the sheet including Data Validation.
  • When the copied sheet is used, an error occurs at the drop down menu of Data Validation.
    • You want to remove this error.

If my understanding is correct, how about this answer? In this answer, in order to remove the error, I overwrite the Data Validation of the copied sheet as a workaround. Please think of this as just one of several workarounds.

The flow for your situation is as follows.

Flow:

  1. Retrieve all data validations from the sheet of Data Entry in the source Spreadsheet ("DataValidationTest") using the spreadsheet.get method.
  2. Copy the sheets of Data Entry in the source Spreadsheet ("DataValidationTest") to the destination Spreadsheet ("Public Destination Sheet").
  3. After the sheets of Data Entry was copied, rename the sheet name from Copy of Data Entry to Data Entry.
  4. Then, overwrite the retrieved data validations to the sheet of Data Entry using the spreadsheet.batchUpdate method.
    • In this case, the structure of data validations retrieved by the spreadsheet.get method is almost the same with the structure for the spreadsheet.batchUpdate method. This workaround used this.
  5. Copy the sheets of Data Validation in the source Spreadsheet ("DataValidationTest") to the destination Spreadsheet ("Public Destination Sheet").
  6. Rename the sheet name of Copy of Data Validation to Data Validation.

Sample script:

When you test this script, please set the variables. And I think that sheet of sheet.spreadsheets.get(), sheet.spreadsheets.batchUpdate() and sheet.spreadsheets.sheets.copyTo() is the same with sheetOps of your script.

const srcSpreadsheet = "###";  // Please set this.
const tempDestSheetId = "###";  // Please set this.
const srcDataEntrySheetId = 0;  // Please set this.
const srcDataValidationSheetId = 123456789;  // Please set this.

let dataValidation = await sheet.spreadsheets.get({
  spreadsheetId: srcSpreadsheet,
  ranges: ["Data Entry"],
  fields: "sheets/data/rowData/values/dataValidation"
});
let data = dataValidation.data.sheets[0].data;
let rows = [];
for (let i = 0; i < data.length; i++) {
  if (data[i].rowData) {
    rows = data[i].rowData;
    break;
  }
}

sheet.spreadsheets.sheets.copyTo(
  {
    spreadsheetId: srcSpreadsheet,
    sheetId: srcDataEntrySheetId,
    resource: { destinationSpreadsheetId: tempDestSheetId }
  },
  (err, res) => {
    sheet.spreadsheets.batchUpdate(
      {
        spreadsheetId: tempDestSheetId,
        resource: {
          requests: [
            {
              updateSheetProperties: {
                fields: "title,sheetId",
                properties: { sheetId: res.data.sheetId, title: "Data Entry" }
              }
            },
            {
              updateCells: {
                rows: rows,
                range: { sheetId: res.data.sheetId },
                fields: "dataValidation"
              }
            }
          ]
        }
      },
      (er, re) => {
        if (err) {
          console.error(er);
          return;
        }
        console.log(re.data);
      }
    );
  }
);

let result1 = await sheet.spreadsheets.sheets.copyTo({
  spreadsheetId: srcSpreadsheet,
  sheetId: srcDataValidationSheetId,
  resource: { destinationSpreadsheetId: tempDestSheetId }
});

let result2 = await sheet.spreadsheets.batchUpdate({
  spreadsheetId: tempDestSheetId,
  resource: {
    requests: [
      {
        updateSheetProperties: {
          fields: "title,sheetId",
          properties: {
            sheetId: result1.data.sheetId,
            title: "Data Validation"
          }
        }
      }
    ]
  }
});

console.log(result2.data);

Note:

  • This script supposes that Sheets API has already been able to be used.
  • This is the simple modified script for showing the flow of workaround. So please modify this for your situation.
  • As an important point, in order to overwrite the data validations to the copied sheet of Data Entry, it is required to execute it after the copy of the sheet of Data Entry was completely finished. So I modified the script like above.

References:

Upvotes: 2

Related Questions