Skip to content
Advertisement

Duplicate whole spreadsheed using Google Sheets API

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?

Advertisement

Answer

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;
}
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement