Duplicate whole spreadsheed using Google Sheets API

Question

I have a spreadsheet with prepared template and a bunch of Apps Script code to work with sheets. I need to export data into this template using Google Sheets API, but doing it in the same spreadsheet for all users is not the best idea. Therefore, I need to create a new spreadsheet for each user. I managed to create an empty spreadsheet, copied template sheet from original spreadsheet and inserted it in a new one like this:

    //Creating new Spreadsheet
    $service = new Google_Service_Sheets($client);
    $serviceDrive = new Google_Service_Drive($client);

    $spreadsheet = new Google_Service_Sheets_Spreadsheet([
        'properties' => [
            'title' => Lang::get('pls.export.spreadsheet_name'),
        ]
    ]);
    $spreadsheet = $service->spreadsheets->create($spreadsheet, [
        'fields' => 'spreadsheetId'
    ]);

    //Copying sheet from template
    $sourceSpreadsheet = 'spreadsheet id goes here';
    $soureSheet = 'sheet id goes here';
    $requestBody = new Google_Service_Sheets_CopySheetToAnotherSpreadsheetRequest(['destinationSpreadsheetId' => $spreadsheet->spreadsheetId]);
    $response = $service->spreadsheets_sheets->copyTo($sourceSpreadsheet, $soureSheet, $requestBody);

This works and I can also write data in it, but the problem is, I can't copy the Apps Script code. I have been also looking for a way to somehow duplicate the whole old Spreadsheet and import the data there, but could not find anything (Not sure if it helps to copy the Apps Script codes though). Is it possible to copy the Apps Script codes using Google Sheets API and insert it in new Spreadsheet? Or how do I duplicate the whole spreadsheet?

Use Drive api? Note that copying code doesn't mean it's the same project-it's a copy and needs to be authorized again. — TheMaster
Is the Apps Script project a standalone or container bound project? — Aerials
@TheMaster Yes, I need to make it a separate Spreadsheet. Then I share it to a logged in user, so they can view and edit things as they want — Vaxo Basilidze
@Aerials The template is a standard google spreadsheet. App script is added manually — Vaxo Basilidze
You want to create a new sheet for each of the users? And will these sheets live in your Drive? Why not for example use the template spreadsheet and create a new internal sheet for each user's data? — Aerials

Vaxo Basilidze Vaxo Basilidze · Accepted Answer · 2020-09-24T09:45:14

I solved this issue by using Google Drive API and copied the file from there instead:

$serviceDrive = new Google_Service_Drive($client);
$drive = new Google_Service_Drive_DriveFile();
$spreadsheet = $serviceDrive->files->copy('your spreadsheet id', $drive);

Then shared the created file to user like this:

$this->insertPermission($serviceDrive, $spreadsheet->id, $user->email, 'user', 'writer');

Works like a charm!

EDIT: Here is the insertPermission function:

function insertPermission($service, $fileId, $value, $type, $role)
{
    $newPermission = new Google_Service_Drive_Permission();
    $newPermission->setEmailAddress($value);
    $newPermission->setType($type);
    $newPermission->setRole($role);
    try {
        return $service->permissions->create($fileId, $newPermission);
    } catch (Exception $e) {
        print $e->getMessage();
    }
    return NULL;
}

Duplicate whole spreadsheed using Google Sheets API

1 Answers