1

I was wondering if there is a API call for Google Sheets where I can delete all cells in a given sheet and paste/replace with a new data. I have the new data in the form of its own sheet. thanks!

JJpajama
  • 19
  • 5
  • maybe this can help? https://developers.google.com/sheets/api/guides/values – Arslan Sohail Bano Jul 21 '22 at 19:17
  • Do you have any specific requirement on how you want to accomplish this? For example, only using the Sheets API, or using Google Apps Script? – Fernando Lara Jul 22 '22 at 00:19
  • Hey, Im using a integrations platform called Make, and they only allow me to make a API call. – JJpajama Jul 22 '22 at 15:22
  • @JJpajama If this answered your question, please click the accept button on the left (check icon). By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved. If the accept button is unavailable to you, feel free to tell me. [How to accept answer](https://stackoverflow.com/help/accepted-answer) – Fernando Lara Jul 28 '22 at 16:46

2 Answers2

1

Update cell values with Google Sheets API

Since you mentioned that you need an API call for this, I think your way to go is to use the batchUpdate method from the Google Sheets API as it is the closest thing to what you are looking for.

With the batchUpdate method you can send a UpdateCellsRequestin order to replace the information from the cells you are looking for. I will leave the official documentation below so that you can check what I just mentioned.

References:

Fernando Lara
  • 2,263
  • 2
  • 4
  • 14
0

For a large existing file, it seems most expedient to delete the file and create a new one with Apps Script.

For delete, you can use DriveApp. Look for methods such as .getFileById(). Or, if you prefer to programmatically search for a filename in a folder, look for .getFolderById() and .getFilesByName(). Once you get the file object, you can either use Drive API's .remove() as shown in this answer or use DriveApp's .File.setTrashed. But note the latter moves file to trash bin, not literally remove from drive.

For file creation, you can use Advanced Drive Service. see this answer for exact steps.

As for populating the new sheet, the OP implies that step is already understood. (No particular data format was provided.) So we are done!

Argyll
  • 8,591
  • 4
  • 25
  • 46
  • Thank you, though i just need to replace one sheet in a large file filled with different sheets and formulas relying on this one sheet... replacing the entire file isnt really an option. Also im not exactly sure how to populate the entire sheet using the API... – JJpajama Jul 21 '22 at 19:28
  • @JJpajama: Then you should ask a separate question, specifying what you need to preserve and define the parameters of your other input data. As well, provide an example sheet there. From your current question, "I can delete all cells in a given sheet and paste/replace with a new data" is the only requirement and there is not much else to comment on. – Argyll Jul 21 '22 at 19:29
  • ah got it. sorry for not being very clear. what i need pasted into the new sheet is found in a XLSX file. – JJpajama Jul 21 '22 at 19:46
  • @JJpajama: Feel free to link me in comment to your new question so I can take a look there. Also, consult guide on [minimal, viable example](https://stackoverflow.com/help/minimal-reproducible-example) as needed. – Argyll Jul 21 '22 at 19:55