Create a new sheet from on other sheet and copy his style (nodejs, googleSheets)

Viewed 55

i'm trying to create a new sheet from an other "template" sheet. But i don't find the correct way for copy the style from the template. It's just return a copy of a sheet without the style (no background-color, no text font, etc...)

Can you help me please?

const readRows = async ({ range }) => {
    const spreadsheetId = '1';
    return await g.sheets.spreadsheets.values.get({
        spreadsheetId,
        range,
        majorDimension: 'ROWS',
    });
};

const updateSheet = async ({ spreadsheetId, range, dimension, values, valueInputOption } ) => {
    const result = await g.sheets.spreadsheets.values.update({
        spreadsheetId,
        range,
        valueInputOption: valueInputOption || SheetInputMode.RAW,
        requestBody: {
            majorDimension: dimension || SheetDimension.ROWS,
            range,
            values,
        },
    });
};

const createNewSheet = async (req, res) => {
    const spreadsheetId = '1';
    const newSheetName = 'test';
    const createNewSheet = await g.sheets.spreadsheets.batchUpdate({
        spreadsheetId,
        requestBody: {
            requests: [
                {
                    addSheet: {
                        properties: {
                            title: newSheetName,
                        },
                    },
                },
            ],
        },
    });

    if (createNewSheet !== null) {
        const template = await readRows({ range: 'template' });

        const updateNewSheet = await updateSheet({
            spreadsheetId,
            range: newSheetName,
            dimension: SheetDimension.COLUMNS,
            values: template.data.values as any,
            valueInputOption: SheetInputMode.RAW,
        });

        res.send(success: "success");
    }
}
1 Answers

You could try spreadsheets.sheets.copyTo instead.

Example Code as seen on the reference link below:

const request = {
  // The ID of the spreadsheet containing the sheet to copy.
  spreadsheetId: 'sourceSsID', 
  // gid in the sheet link
  sheetId: 0,  
  resource: {
    // The ID of the spreadsheet to copy the sheet to.
    destinationSpreadsheetId: 'destinationSsID',  
  },
  auth: authClient,
};

try {
  const response = (await sheets.spreadsheets.sheets.copyTo(request)).data;
}

Sample source:

sample

Sample output:

output

Reference:

Related