Duplicate whole spreadsheet 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
– TheMaster, Commented Sep 22, 2020 at 12:16
Is the Apps Script project a standalone or container bound project? — Aerials
– Aerials, Commented Sep 22, 2020 at 12:52
@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
– Vaxo Basilidze, Commented Sep 22, 2020 at 12:52
@Aerials The template is a standard google spreadsheet. App script is added manually — Vaxo Basilidze
– Vaxo Basilidze, Commented Sep 22, 2020 at 12:53
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
– Aerials, Commented Sep 22, 2020 at 13:01

Vaxo Basilidze · Accepted Answer · 2024-10-30 10:41:09Z

1

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;
}

edited Oct 30, 2024 at 10:41

answered Sep 24, 2020 at 9:45

Vaxo Basilidze

1,05716 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Datadimension Over a year ago

OP specifically asked for Sheets API

Vaxo Basilidze Over a year ago

@Datadimension I'm the OP, I know what I asked for and I answered my own question :)

Datadimension Over a year ago

My apologies, if you can slightly edit your answer, I can happily remove the downvote as its locked by Stackoverflow

Vaxo Basilidze Over a year ago

@Datadimension Hmm, downvote and upvote should work fine even when the answer is marked correct

Datadimension Over a year ago

It complained on my phone but seems to have accepted on PC

Collectives™ on Stack Overflow

Duplicate whole spreadsheet using Google Sheets API

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related